Dynamic compare almost similar columns and output result in new column

[jkschola]

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

RowID	Name	City	Email	Country
1	Amazon	Paris	info@amazon.com	FR
2	FB	New York	info@facebook.com<	US
3	IG	NY	info@IG.com	USA
4	Dior	London	info@dior.com	UK

Source 2: Uploaded data after cleasing

RowID	Name_ERP	City_ERP	Email_ERP	Country_ERP
1	Amazon	Paris	info@amazon.com	FR
2	Facebook	New York	info@facebook.com	US
3	Instagram	New York	info@instagram.com	US
4	Dior	London	info@dior.com	UK

Output: What was changed

RowID

Name

Name_ERP

Name Check

City

City_ERP

City Check

Email

Email_ERP

Email Check

Country

Country_ERP

Country Check

1

Amazon

Amazon

TRUE

Paris

Paris

TRUE

info@amazon.com

info@amazon.com

TRUE

FR

FR

TRUE

2

FB

Facebook

FALSE

New York

New York

TRUE

info@facebook.com<

info@facebook.com

FALSE

US

US

TRUE

3

IG

Instagram

FALSE

NY

New York

FALSE

info@IG.com

info@instagram.com

FALSE

USA

US

FALSE

4

Dior

Dior

TRUE

London

London

TRUE

info@dior.com

info@dior.com

TRUE

UK

UK

TRUE

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!

[SPetrie]

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.

[jkschola]

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

[SPetrie · Accepted solution]

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.

[jkschola]

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.