Hello,
I'm pretty new to Alteryx and this is my first time posting here so thank you in advance for your patience.
I am trying to figure out how to how categorise a country field. Sounds like it should be straightforward right?
I have a field name, 'country' which could contain anything from 'UK' to 'UK,ROI,Spain,Germany,Italy,France' etc. the list could cover as many countries as you can think of. What i would like to achieve is to have a field which is populated with either 'domestic' or 'international'.
I.e.
if the country field = UK or ROI or UK & ROI or ROI then i would like the new field to say domestic.
if the country field doesn't contain UK or ROI at all then i would like the new field to say international
If the country field contains Spain,Germany,ROI,Italy,France,UK, i need to be able to flag this for review.
I have tried a nested IF contains formula but it seems to stop searching through the field as soon as it gets a match. I have tried parsing the data into different columns and using a multi-field formula but that doesn't seem to allow me to apply the result to a single field. I have even tried using a number of filter tools but the country names could be in any order so a simple filter may not work.
Please let me know if anyone knows how i can get around this issue.
Thank you all in advance.
Solved! Go to Solution.
hello Mark,
The If statements you are requesting are slightly contradicting. For example if the country contains ROI would you want it to say 'Domestic' or 'Review'?
Here is a starter to the If statement that you need to write.
IF Contains([Country], "UK","ROI") Then "Domestic" Else "International" ENDIF
If you provide the some data I can send you a completed workflow.
Thanks,
Nick
Hi Nick,
This is the issue, it is contradictory, so if the country field contains either 'UK' or 'ROI' or 'UK,ROI' or 'ROI,UK' then it should be 'domestic', everything thing else should be 'international' except if the country field contains 'UK' or ROI' along with other countries.
Please see sample data.
Thanks for your quick reply.
Mark
This If Statement should work:
IF [Country] = 'UK' OR [Country] = 'ROI' OR [Country] = 'UK,ROI' OR [Country] = 'ROI,UK' THEN 'Domestic' ELSEIF CONTAINS([Country],'UK') OR CONTAINS([Country],'ROI') THEN 'Review' ELSE 'International' ENDIF
Hi Nick,
Looks like you have a new version than me. I'm on 10.6, but used the if formula suggested (I couldn't work out how to create a nested if with or's) but it looks to be working perfectly.
Thanks for your help. I really appreciate it.
Mark