Retaining Duplicates in Comparison of 2 Files using Join Tool

FChen1 — Tue, 24 May 2022 20:03:32 GMT

Alteryx Community - I am trying to compare two very similar files to determine if there are any records from one file that is not present in the other file (and vice versa). Thus, using Join tool to identify "L" and "R" outputs. However, the issue with the two files is that there are multiple records that are exactly the same. There is not a unique identifier in either file or even multiple fields that can be "concatenated" to make a unique identifier. Further, there are "duplicates" of the same records in both files, but more importantly, these duplicate records cannot be removed through a Unique tool, as an example. The data in each of these duplicate records is necessary to validate the total amount.

For example, below, there are 7 records in the "L" and "R" input files. Two of the records are duplicates (B 9 and B($) 9). Cannot remove the duplicate record because the total sum at the bottom will be incorrect.

Any suggestions on how I can compare the "L" and "R" input files, identify the "L" and "R" specific outputs, join them together to make the Final Desired Output?

Thank you!

Re: Retaining Duplicates in Comparison of 2 Files using Join Tool

phottovy — Tue, 24 May 2022 20:19:03 GMT

Hi @FChen1 ,

Here is one possible option. I added sorts and multi-row tools to both sides. For each duplicate value, I create a unique row ID and then use that in my join. This allows you to join on each duplicate row that exists on both sides without creating a bunch of extra rows on the join.

Re: Retaining Duplicates in Comparison of 2 Files using Join Tool

FChen1 — Tue, 24 May 2022 21:27:20 GMT

Thank you!

topic Re: Retaining Duplicates in Comparison of 2 Files using Join Tool in Alteryx Designer Desktop Discussions

Retaining Duplicates in Comparison of 2 Files using Join Tool

Re: Retaining Duplicates in Comparison of 2 Files using Join Tool

Re: Retaining Duplicates in Comparison of 2 Files using Join Tool