Joining datasets, but once a match is made, it can no longer be used

[PeterAP]

Hi!

I have two datasets of transactions - one for debit transactions and one for credit transactions.

I need to join these fields together so that I can identify any credit transactions which don't have a debit. The fields that can be joined on are "Ref No.", "Company" and "Amount". However, this is not a unique key, so there could be multiple matches. As such, I don't think a normal join tool can work in this situation.

Therefore I need to perform a join so that each debit transaction can only be matched against one credit transaction only - and then is no longer taken into consideration.

Hopefully that makes sense - can anybody help?

[jdunkerley79]

Could you post some sample data? It will make it easier to give a concrete answer.

Assuming matching on Amount

I would suggest adding a key field - equal to the ABS(Amount) and then a RecordID (computed using a MultiRow field grouped by Amount) for the Debit set and Credit set

you can then do a join based on this key field which would ensure each is only used once.

Hope it gives you enough to get going

[PeterAP]

Hi @jdunkerley79 , thanks for your response.

I've attached some sample data for the credit table, debit table and the desired output. I hope this helps!

[jdunkerley79 · Accepted solution]

I'd suggest:

So using a multi-row tool grouped by RefID and Quantity, add a RecordID to both Credits/Debits

Then join on RefID, Qty, RecordID

The J will contain the match

The L will contain the unmatched Credits

The R will contain the unmatched Credits

Adding a Union tool to join make a full set of unmatched

Sample attached