Join tool should not multiply records

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.

Dataset 1.xlsx

Dataset 2.xlsx

Dynamic Processing

Accepted answers

All comments

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.

Join Workflow.yxmd

Output.xlsx

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

Join.yxzp

vrishabhpatel

Dataset two is missing two entries. Other than that looks fine.

Can you also explain briefly how the macro works? This is just a part of my requirement, I want to scale this to include other parameters in join

jdminton

I think there are some issues with the data. Based on the datasets provided, the amounts will only match if you switch the customers. This may have happened when you tried to mask the customer names with ABC and PQR. I switched them in dataset two in case that is true. The join issue you are having is due to not using enough fields to make your join unique. I've attached a workflow that matches the 8 you were looking for based on the sequence number (Sr.No.) and customer name with amount. the other two items are unioned back in if you want them together. If not, just remove the union from L and R side of the join output. Let me know if this helps or if my assumption isn't what you were wanting.

Join Workflow.yxzp

vrishabhpatel

@jdmintonThat's precisely my problem. I do not have any unique key to match the amounts. All I have is the customer name amongst which the amounts should be matched across both the database. Using Sr. No. as a key won't work since I could have thousands of customers with millions of line items with varied amounts because only in this case the entries are lined up as per Sr. No. In my actual data I could have many other amounts so Sr. No. of same amounts in both dataset could be different. I will not face a challenge with amounts which are unique and appearing only once. I face a challenge to match entries with repeating amounts.

jdminton

I understand. I've been there on reconciliations for various vendor accounts. Try this workflow. It assigns a sequencing number based on amount and customer so that the first 10,000 amount matches against the first 10,000 invoice, etc. You may have to create a new field in front of the join to round the values and switch them to string if your system contains multiple decimal values, but it works well with the data you provided. Please let me know if this helps.

Join Workflow.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703