Removing duplicates after Join

[hemant86]

Hi Team,

Have a scenario where I have 2 files as input. Need to do file to file recon. Both the files have columns with Emp iD, Amount , Bank no. and Account no. Now I have to perform a recon to verify if there is a change in Bank no. or Account no when previous month file is compared with current month file. Now the issue is I cannot have a unique key hear to perform a join. There will be multiple entries for an employee in both the files and when I take Emp ID as key when joining I get duplicates which is natural. I cannot use Bank no. & Account no combined with EMP ID since Bank no. & Account no is something I'm validating. 

So appreciate if any suggestion around it. How to eliminate duplicate entries from the output. If not eliminate what would be the best way to represent the data. Attaching a sample workflow for your reference.

[cjaneczko]

If you are validating the non matches, why not join on the account no and then Union the Left and Right outputs of the join into an output. This will give you the ones that dont match up on both sheets. Ignore the Joined accounts as those are the same and don't need to be reviewed. 

[ArnaldoSandoval]

Hi @hemant86 

Please take a look at the enhanced workflow, it identifies changes in Bank No and Account No.

hth

Arnaldo

[hemant86]

Thanks for your response @cjaneczko . There will be also cases where employee would have left the org. or there might be new joiners. So I need to identify those as well. So I have to go with Emp ID.

[hemant86]

Thanks @ArnaldoSandoval . This is a good solution. Will have to check with the user though. If they want it in the same sheet highlighting which are matching and non-matching case. Thanks again for the solution.