Categorising a country field

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.

Output

Expression

Interface Tools

Accepted answers

NickC

Hello Mark,

Please see workflow attached.

I used a combination of a left outer join and an if statement to complete this. Let me know if this is not what you are looking for.

Nick

Workflow is version 11.5

Country Workflow.yxzp

MarkGuacci

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

All comments

NickC

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

MarkGuacci

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

Sample Data.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703