Retaining Duplicates in Comparison of 2 Files using Join Tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you!
