Join quantity of matched records?

[Julie_Taylor]

I have two tables: Primary table called Payments and secondary table called Invoices.

Payment Table fields

• Customer number
• Amount
  - [Customer number] [Amount] concatenated

Invoice Table fields

• Customer number
• Amount
• Invoice number
• [Customer number] [Amount] concatenated

I may have 3 payments for a given customer that I need to match to an invoice number; however, there may be 5 invoices. I want to look up 3 of the 5 invoices to find the corresponding invoice number.

My problem is that when I combine tables, I get all 5 invoices because they all match my primary key ([customer number] [amount] concatenated).

Is there a way I can tell Parabola, if I have 3 records in one table, do not find more than 3 matches in the corresponding second table?

This is driving me nuts!

Thank you for any help you can provide!!

[sachi]

Hey @Julie_Taylor !

Thanks for posting your question. I think I follow. For the invoice table, where you might have a few invoice numbers for a given ([customer number] [amount] *concatenated* ), how should we determine which invoice number to match? Are you considering ([customer number] [amount] concatenated) values to be unique?

Depending on what you need, one option might be to use a Remove duplicate rows step before the Combine tables step to make sure you only have 1 invoice number per ([customer number] [amount] concatenated) value. This will ensure that we only append 1 invoice number when using the Combine tables step.

Would that work?

[Julie_Taylor]

We have a lot of payments from the same customer on the same date for the same amount. Our payment file (unfortunately) does not contain an invoice number. So we concatenate the customer and the amount to reduce the number of possible matches.

So our payment file might have 3 $5 payments:
ID-customerABC-5
ID-customerABC-5
ID-customerABC-5

We need to match it to our open invoices file which might have 4 $5 open invoices:
ID-customerABC-5
ID-customerABC-5
ID-customerABC-5
ID-customerABC-5

I need to apply the 3 payments to 3 of the invoices and leave one invoice unmatched.

Does that make sense?

[sachi]

Hey @Julie_Taylor - I created some dummy data to demo what I think you’re trying to accomplish.

Here’s my invoice table:

Here’s my payment table:

Then, I used the Combine columns step to create the matching column (concatenating Customer Number and Amount)

Here’s my updated invoices table:

Here’s my updated payments table:

Then I used the Combine Tables step where the Invoices table is actually my primary table. I set it up like this where Invoices is my primary table and I select to Keep all rows in all tables.

You’ll see that Matching column (1) gets appended for the invoice/payment matches and that fourth invoice is unmatched.

Did I understand your data and your goals correctly? If not, clarify for me and I can readjust!

[Julie_Taylor]

You have the general idea and are almost right.

In your customer column you have Cust 1, Cust 2, Cust 3. But I may get 3 payments from Customer 1, all of them $5.

In the other table, I have 4 open invoices for Customer 1… all of them also $5.

[sachi]

Got it. This does make it more complicated, but is do-able Here’s what the flow is going to look like.

Essentially, for both the Invoices table and the Payments table, we need to create a counter column that counts for each unique customer-amount combination, how many invoices there are and how many payments there are. That’s what all the steps in the two branches you see in my screenshot are doing. Once we have that, we can combine that tables and do some final cleanup. The end result of this flow looks like this:

Amount owed is the invoices column and Amount paid is the payments column.

Here’s a snippet you can copy and paste (using your keyboard shortcuts) to paste a copy of these steps onto your own flow: parabola:cb:b9ef72fa51974911be0e9685cd96eb3f

You can click into each of the steps to see what they’re doing. Hope that helps!

[Julie_Taylor]

It worked!! I’m so excited; thank you for solving that. I NEVER would have been able to figure out that path to creating sequential numbers. Pure genius!

Thank you, thank you!