Comparing two datasets with same headers to figure out changes before and after

Question

Hi everyone,

I’m new to Alteryx and need some assistance with comparing two datasets that have the same headers. My goal is to identify changes between the datasets, specifically to highlight:

* Any new lines added in the "after" dataset
* Any lines removed from the "before" dataset
* Any changes in the existing lines
* Any lines that remain the same

The order of the rows is important, and there is no unique ID to use for comparison. I have attached sample datasets for "before" and "after" in separate tabs, as well as an "expected output" tab for reference.

Could someone please guide me on how to achieve this? Thank you so much!

Sample data.xlsx

novanhutran27 · Answer

Hello,

Thank you very much for your prompt response. It’s challenging without a unique ID for comparison and identification, but I truly appreciate your effort to assist!

Best Regards.

TUSHAR050392 · Answer

Hey @novanhutran27 ,

This is a bit tricky and I am not sure if this can be achieved because to compare records from two tables, you need some sort of match key and in your case there can be a lot of combinations especially when you are trying to look for added, removed and modified category.

I think the best can be done is categorizing them into Same and Mismatch(this will contain added, removed and modified). For this, generate a new column in both your datasets and name it match key which will be a concatenation of all your columns which you can do in a formula tool by doing Column1 + Column2 + Column3 etc. Then join the two datasets New one to the left and Old one to the right. The records that will join will be the same category and not joined in L and R side will be mismatch which will require further investigation if they were added, removed and modified.

Thank you.