Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Categorising a country field

MarkGuacci
6 - Meteoroid

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.

5 REPLIES 5
NickC
Alteryx Alumni (Retired)

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
6 - Meteoroid

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

nick_ceneviva
11 - Bolide

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
NickC
Alteryx Alumni (Retired)

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.

country.png

 

 

Nick

 

Workflow is version 11.5 

MarkGuacci
6 - Meteoroid

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

Labels