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

Compare Rows of Data from 2 files.

Charlie_J
5 - Atom

Hey Guys,

 

I have 2 excel files containing sales information and i need to perform a reconciliation to make sure they match 100%. I need a workflow that will compare every row from 1 file against every row from the other file and isolate any row that only appears in 1 of the files. I have each file coming in from a different input and have attached examples of what my data looks like. I also import each file with the full file path so i know where each row of data came from. The bottom 2 rows in example 2 do not appear in example 1. Typically these files will contain hundreds of rows so i need a way to isolate unique rows. Any help is much appreciated. 

 

 

 

7 REPLIES 7
IraWatt
17 - Castor
17 - Castor

Hey @Charlie_J,

You can use the join tool to compare all the rows from each data set:

IraWatt_0-1656055164172.png

(shown by the arrow) I joined on every column I wanted to compare (in this case all of them). Any row which is unique to the left file will appear in the left output of the join. Any row which is unique to the right will appear in the right output. 

 

How many columns will your data set have?

 

Any questions or issues please ask :)
HTH!
Ira

Jotigautam
10 - Fireball

you can use join tool to get the rows that don't appear in both files and use another join tool for each file to exclude those rows and use append tool on the rest of the rows to reconcile.

 

Jotigautam_0-1656055435191.png

 

cvalteryx
7 - Meteor

Hey @Charlie_J ,

 

This might help too!

 

cvalteryx_0-1656056058699.png

 

grazitti_sapna
17 - Castor

Hello @Charlie_J, there is another way of solving this problem in case you have hundreds of columns to match then you can use this workflow if it fits your scenario.

grazitti_sapna_0-1656057682570.png

In case you have something specific please let us know.

 

Thanks!

 

Sapna Gupta
Charlie_J
5 - Atom

This worked perfectly, thank you so much. 

grazitti_sapna
17 - Castor

@Charlie_J, can you please try my workflow as well if it fits your requirement?

 

Thanks!

Sapna Gupta
IraWatt
17 - Castor
17 - Castor

No worries @Charlie_J 😄, I would look at @grazitti_sapna solution also. It works a lot better with data with a lot of columns.

Labels