Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!
community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

Dynamic compare almost similar columns and output result in new column

jkschola
5 - Atom
‎04-26-2022 12:43 AM

Hi community !

 

I am new to Alteryx and  I need your help.

 

I have to compare the data from two different sources. See sample datasets and my workflow below.

Columns are like : Name from source 1, Name-ERP from source 2, Name check ( TRUE or FALSE) as my result comparison.

 

Source 1: Given data before cleansing

RowIDNameCityEmailCountry
1AmazonParisinfo@amazon.comFR
2FBNew Yorkinfo@facebook.com<US
3IGNYinfo@IG.comUSA
4DiorLondoninfo@dior.comUK


Source 2: Uploaded data after cleasing

 

RowIDName_ERPCity_ERPEmail_ERPCountry_ERP
1AmazonParisinfo@amazon.comFR
2FacebookNew Yorkinfo@facebook.comUS
3InstagramNew Yorkinfo@instagram.comUS
4DiorLondoninfo@dior.comUK

 

Output: What was changed

 

RowIDNameName_ERPName CheckCityCity_ERPCity CheckEmailEmail_ERPEmail CheckCountryCountry_ERPCountry Check
1AmazonAmazonTRUEParisParisTRUEinfo@amazon.cominfo@amazon.comTRUEFRFRTRUE
2FBFacebookFALSENew YorkNew YorkTRUEinfo@facebook.com<info@facebook.comFALSEUSUSTRUE
3IGInstagramFALSENYNew YorkFALSEinfo@IG.cominfo@instagram.comFALSEUSAUSFALSE
4DiorDiorTRUELondonLondonTRUEinfo@dior.cominfo@dior.comTRUEUKUKTRUE

 

I am looking for a dynamic solution as I have twenties of columns from each dataset 

 

Please note RowID that can be used for RecordID.

 

Thanks for anyone who can help!

Help_Dynamic Fields comparison.yxmd
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

4 REPLIES 4
SPetrie
12 - Quasar
‎04-26-2022 11:22 AM

Do the columns\rows always line up in that order? RowID 1 will always correspond to recordID1?

If we are sure that we wont be comparing Amazon to Facebook by accident, you can go a dynamic formula with the CReW macro.

SPetrie_0-1650997221536.png

SPetrie_1-1650997266933.png

 

 

Reply
jkschola
5 - Atom
‎04-26-2022 01:28 PM

Thank you @SPetrie for suggesting your solution.

 

As you asked, all the columns from the source ERP have that suffix _ERP after the field name to know the source of data. That's the only difference in column names compared to Raw Source data.  

I put that RowID to demonstrate that RowID is the primary key as they are unique in both datasets. No risk of confusion.

 

To test your solution, can you please attach your workflow for testing and better understand your logic.

 

Don't hesitate to ask any clarification.

 

Thanks

Reply
0 Likes
SPetrie
12 - Quasar
‎04-26-2022 02:55 PM

Here is the workflow.

You are going to need to have the CReW macro pack installed for it to work since it uses the dynamic formula macro.

Help_Dynamic Fields comparison v2.yxzp
Reply
jkschola
5 - Atom
‎04-28-2022 04:50 AM

Thank you @SPetrie  it's really working with the example i gave you. I'll see if I have any issues with the whole datasets.

 

Reply
get your questions about inspire answered by inspire attendees
learn more about the python upgrade coming in may 2024
Labels