Join tool should not multiply records

[vrishabhpatel]

I have two data sets which I need to join basis the amount appearing in each line item. For instance, I could have 10 line items having amount as 100 in dataset 1 and 8 line items having amount as 100 in dataset 2. Join tool generates 80 line items as a result of this. What I need is a workflow to join a unique row in dataset 1 with another unique row in dataset 2 and so on. As an output I should have 8 line items individually matched, with 2 additional line items from dataset 1.

I tried multiple functions such as summarize, unique, but to no avail.

I have attached sample datasets. They contain two unique amounts which must be matched with one another (Consider TDS amount / value). Currently I am getting 160 line items after joining. What I need is only 18 line items where 14 have matched and 4 remain unmatched. I will have to do the join basis amounts only since that is what I am trying to match.

[RSreeSurya]

Hi @vrishabhpatel ,

Could you please share the output sample also, for the above attached files. 

Which columns you want to consider to join??

[vrishabhpatel]

Have attached the workflow. Also have attached an excel file with Current Output and Expected Output.

I need to use the TDS value / TDS amount column to do a join. Since I am identifying entries basis amounts. That is the only unique key I have.

[RSreeSurya]

As we have multiple TDS Value/ TDS Amounts for first row where value is 100000 , from dataset which 100000 you have pick-up as we have multiple 100000. What logic you have used for selecting there ?

[vrishabhpatel]

First in first out basis the date.

[RSreeSurya]

Out of these two screenshots for 2023-06-02 from screenshot 2 , which row you will be selecting as per your logic?

So for all the highlighted rows in screenshot one , first of screenshot 2 will be the output ??

Could you please confirm @vrishabhpatel 

[RSreeSurya]

Based on which date column , as we have multiple date columns 

[vrishabhpatel]

2023-06-02 gets matched with 2023-07-02

2023-06-03 gets matched with 2023-06-02

2023-06-04 gets matched with 2023-07-03

Essentially what I need is, once two records are matched they need not be considered again for matching other amounts.

[vrishabhpatel]

Invoice date in Dataset 1 and transaction date in Dataset 2.

[RSreeSurya]

Attaching the sample workflow. Hope this fulfils the requirement. 

Please have a look @vrishabhpatel