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

Replacing the Regions in one file with the regions in other file.

stj1120
8 - Asteroid

Hi all,

 

I'm trying to replace the Regions that are in the first input file with the regions that are in the second input file.

 

These regions should be mapped basing on their business area with the respective country. The regions present in the first input file are the older regions and the regions in the second input file are the newer regions.

 

How do I replace those regions? 

 

sreenivasateja_1-1589368047633.png

 

I have attached the two excel files and kindly check and let me know solution.

 

Thank you.

 

Best Regards,

Teja.

5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

Hi @stj1120 I mocked up a workflow let me know what you think?

stj1120
8 - Asteroid

Hi @JosephSerpis 

 

Thank you for your quick answer.

 

But, if I want to use only Find & Replace in this context instead of using join tool then how do we do that? Kindly let me know.

 

Awaiting your response.

 

Thank you.

 

Best Regards,

 

 

 

estherb47
15 - Aurora
15 - Aurora

Hi @stj1120 

 

You'll need to configure the Find and Replace tool to use the mapping based on business area (in the screenshot, looks like you're mapping to regions instead). So whatever the key fields are, that's what you want to match on. And both of those fields need to be string data types (length doesn't matter)

In the bottom half of the box, choose to Append the new name you want to use. Then use a Select tool afterwards to deselect the old name that you don't want to move forward.

 

The advantage of Find and Replace is it's faster. The disadvantage is it only works on text, only on one match, and doesn't detail what doesn't match. Although Joins can be a bit slower, I often favor them because of their flexibility. 

I'd likely start with @JosephSerpis 's approach and see if it's fast enough before going ahead with Find/Replace, but that could also just be my bias for the more thorough tool.

Let me know if that helps

Cheers!

Esther

stj1120
8 - Asteroid

hi @JosephSerpis 

 

Also when I checked the output,  Business Area is not showing all regions with respect to the Regions.

 

Actually, the Region and Business Area fields, Country, and BusinessArea Key  columns in the Main file -- Find.xlsx should be replaced with  Region Text, and BA Text fields from the Replace File.xlsx.

 

Replace File.xlsx                            Main file -- Find.xlsx   

Region  Text               --->                 Region

BA Text                          --->              Business Area

End user country Text   --->              Country

Business Area               ---->            BusinessArea Key

 

which means Main file -- Find.xlsx should show the same kind of output as like Replace File.xlsx.

 

Kindly please help. 

 

Awaiting your response.

 

Thank you.

 

JosephSerpis
17 - Castor
17 - Castor

Hi @stj1120 I revised the workflow however I was unsure regrading the country aspect of your requirements as I saw that the countries don't match and causes a number of nulls when I attempted to replace with the data in the replace field.  

Labels