Is there a tool I can use to generate an outcome other than using Nested If statements?

[timoyd]

Hello Everyone,

I am trying to build a workflow that would tell what country it is based off numbers. I can make it by using nested IF statements but curious to know if there are any other alternatives.

Scenario: I have 3 countries Japan, USA, Singapore and I have numbers 1-12. If the Number 1,3,4,5 then Japan. If 2,6,8,10 then USA......etc.

Please advise 

[BrandonB]

Do you have a mapping table that either shows what numbers join to what countries or at least the ranges? You could likely join this other dataset to link it, or you may need to do some parsing and a generate rows tool if you have a range listed. 

[apathetichell]

I'd use a nested if (ie...)

if tostring([countryvalues]) in ("1","3","4","5") then "Japan" elseif tostring([countryvalues]) in ("2","6","8","10") then "USA" else "singapore" endif

[apathetichell]

if there's a repeatable mathematical formula (floor division/mod./ranges) you can use a switch() command...

[shreyanshrathod · Accepted solution]

Hi @timoyd ,

If you have limited number of countries, I would suggest to build a custom Lookup table (maybe .yxdb or in a Database).

Use this lookup table and JOIN based on numbers and get the respective country name.

If you are using this Formula tool (Nested IF) statements in multiple scenarios (let's say over 10's of workflows), imagine one day if there is a change in country (e.g. number 3 changed from Japan to USA), then you will have to go through each Nested IF statement and make the change. But if you use this lookup table, you only need to change in 1 place, i.e the lookup table itself.

Hope it helps.

Regards,

Shreyansh Rathod

[timoyd]

The numbers are provided in the data set but the country is not. I have to add a new column to provide the country

[timoyd]

@shreyanshrathod  that is my other option to build a custom table and do a Join. I was just wondering if there were any other tools that could be used.

Thank you for this option as I think this is the most efficient way.

[apathetichell]

The key question is - is this repeatable and how much will the information change over time. How scalable does it need to be? If it needs to be scalable then it's going to be easiest to make a lookup table and join. If its going to be done with 3 entries and fairly straightforward - use the attached.

[shreyanshrathod]

@timoyd ,

In case you are extremely interested to use a different tool, you can use 'FIND AND REPLACE'😂