Need help identifying data combination issues and correcting them

[Jack5]

Hi all,

I have two fields in a large data set that have variable inputs that are supposed to combine a certain way. From the instructions:

     Region:                                                 Country:
Europe                             France, Germany, Poland, Spain, Sweden, United Kingdom
North America                 Canada, Mexico, United States
South America                 Argentina, Bolivia, Brazil, Chile, Columbia, Venezuela

The country should be matched to its proper region, ex. Country - USA  then  Region - North America

Some the combinations in the dataset are wrong however, ex. Country - USA  then  Region - Europe

My question is, what tool/formula can I use in alteryx to identify these incorrect combinations and correct them in accordance with the chart above?

My file is attached. I already tried to create a formula with the formula tool to solve this issue but it didn't work. The formula tool I tried with is annotated ALTERYX COMMUNITY QUESTION.

Please help if you can.

[MichalM · Accepted solution]

@Jack5 

I'd create a Lookup table with the desired mapping between the Country and a Region and use the Join to append the correct Region - see the below and attached

[Jack5]

Thanks for your response @MichalM.

I've attached an updated file, adding in the tools you input on the separate sheet. I changed a couple small things to format to my data. Does it look like I incorporated all of those tools correctly?

[MichalM · Accepted solution]

Yes you did. What you could do is to get rid of the Text to Columns and Data Cleansing - I only used it to get the cleaned up lookup table using the one you provided. 

Attached is what I have in mind

[Jack5]

That all works, thanks @MichalM for the help there. While we're at it, there is one more thing in my file I know needs to be fixed. In my "Join Multiple" tool that is furthest to the right (I've annotated it "TOO MANY RENAMED FIELDS"), there are a lot of fields from Input #2 that have been renamed, but that is not supposed to be the case. I'd appreciate it if you could help me make it to where that wasn't the case, thanks.

[MichalM · Accepted solution]

As you're only joining two streams of data, using the Join module will suffice. This will only rename the duplicated fields - Model and Series in this instance if you choose to include them twice.

An alternative would be to use the Dynamic Rename module and configure it to remove the "Input_#2_" Prefix

In both cases, you'll also need to reconfigure the Union following this step and use the Auto Config by Name option

[Jack5]

That also worked perfectly, thanks again.