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.

[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.

[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.