Join Tool Issue

Question

I have an Alteryx application that is checking for duplicates and similarities between two txt files and outputting to excel in multiple different sheets based on similarities/differences.

I am having an issue with the join tool at the end of my workflow. Incoming from the left i have 369 Records and from the right 364.

The output from the Join tool only has 7 records from the left, 2 records from the right, and 362 from the inner join.

Is there a way to have all records that are duplicate output from the inner join? I have to do further filtration to the inner join data on fields I did not join the 2 instances with, so I need all 700+ records.

I have attached screenshots below of the workflow.

Thank you for any assistance!

Alteryx help - discussion.PNG

andrewc22 · Answer

@mceleavey  I am joining them based on key columns (which does not include dates) which will output to an excel sheet. Next based on key columns, take the duplicates and on another sheet output which records have the same key columns but different dates.

Essentially I have these 5 excel sheets:

Unique to instance 1

Unique to instance 2

Key columns match but differ on dates

All columns match

All records from instance 1 and 2

andrewc22 · Answer

I tried deleting and refreshing, but same result.  @skeen503979

Attached is each input and output with record #

Right output - Alteryx.PNG

Inner Join output.PNG

Left output - Alteryx.PNG

Left input - Alteryx.PNG

Right Input - Alteryx.PNG

mceleavey · Answer

Not really. Can you provide a mock up of what you're trying to achieve?

If you want to see the duplicates from the inner join, you need to determine what you mean by a duplicate. Is it where all fields are exactly the same? The key fields are the same?

You can start by using a sum tool and grouping by the fields you want to define as a duplicate if they are the same, including a count to show how many records are in that group. Then filtering out the records where that count =1. You're left with the "Duplicates".

M.