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.
Hi @vrishabhpatel ,
Could you please share the output sample also, for the above attached files.
Which columns you want to consider to join??
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 ?
First in first out basis the date.
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
Based on which date column , as we have multiple date columns
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.
Invoice date in Dataset 1 and transaction date in Dataset 2.