Reconciling - Duplicates

[JohnDella]

Hello Alteryx Community,

I was hoping for some assistance in reconciling Duplicates.

I have two data sets that I need to reconcile with each other (Bank Rec). I usually use the join toll to reconcile, however when I do, duplicates from Data Set  #1 are being joined more that once with Data Set #2. I would like there to be some kind of rule that stops this from happening, by having the data in the duplicate only be recognized once. Please see below the data sets (LEFT) and the hopeful solution(RIGHT):

DATA SET #1			DATA SET #2
Date	Deposit		Date	Deposit		Date	Deposit	Source	OPEN
08/07/2020	$3,000.00		8/7/2020	$3,000.00		08/07/2020	$3,000.00	Data Set 1	OPEN
08/14/2020	$3,000.00		8/14/2020	$3,000.00		08/07/2020	$3,000.00	Data Set 1	OPEN
08/07/2020	$3,000.00		8/20/2020	$3,000.00		08/07/2020	$3,000.00	Data Set 1	OPEN
08/14/2020	$3,000.00		8/20/2020	$3,000.00		08/14/2020	$3,000.00	Data Set 1	OPEN
08/07/2020	$3,000.00		8/20/2020	$3,000.00		8/20/2020	$3,000.00	Data Set 2	OPEN
08/07/2020	$3,000.00					8/20/2020	$3,000.00	Data Set 2	OPEN
8/20/2020	$3,000.00

I perform this reconciliation weekly and these numbers and dates will change continuously.

Please let me know if you have any ideas to help me solve this.

Thank you!

[morr-co]

Hi @JohnDella - I would use a Unique tool to isolate unique records based on the Date and Deposit. This would allow you to run unique rows through the join tool. You could also see which rows are duplicated in the D output.

[JohnDella]

Hey @morr-co  that definitely helps me with the unique values.

How would you recommended me to join the duplicate values. I was thinking of brining in a Record ID, but that doesn't work as all the duplicates are together.

Any advice is helpful  

[Qiu]

@JohnDella 

Can you elborate your logic on the "join" here?

Say 8/20/2020 from DateSet#2, it has 3 entries there, but after your "join" only 2 left.

[JohnDella]

@aiu Hello, I actually figured out how to do this will the mult-row function and using a record Id. Thank you for your response