Join based on tolerace level

Question

So I have two datasets and I have to match the value based on account number, currency, and amount but the tolerance should be 10 cent. Example:

Dataset 1:

Account currencyamount1234USD10.20

1238USD10.10

2671EUR45.20

Dataset 2:

Account currencyamount1234USD10.30

1234USD10.00

2671EUR45.201238USD10.40

Output:

Account currencyamountResult1234USD10.20

Present

1238USD10.10

Not present

2671EUR45.20Present

1st row is present because the difference is +/- 10 cents and in dataset 2 the value is 10.30 (1st row), 2nd row is not present because 10.10 is not matching with 10.40 because our threshold is +-10 cents So 2nd line is not present

SeanAdams · Answer

I kinda agree with @AngelosPachis  and @Qiu

* I would first do an exact join on Account; Currency and Amount - that gets rid of the easy exact matches
* The second join should be a join based on account and currency only* You will then get a join explosion - i.e. you'll get multiple things joining to a given rows
* You can then use the method that @Qiu and @AngelosPachis to look for possible matches within a threshold.

When doing work like this (reconciliations and partial-matches) - it's always important to look for duplicate matches.

For example - if data set 1 has USD: 10.00; and dataset 2 has two of these - then you need to spot that you are missing a match on the second one.    This happens often when transactions have a common value.

Qiu · Answer

@Sshasnk 
Hope this helps

New Workflow3.yxmd

AngelosPachis · Answer

Hi @Sshasnk ,

You can use a join tool to bring the two datasets together and join on the fields [Account] and [Currency].

Note that out of the J output anchor you will get two rows for account 1234 with USD, one for the amount of 10.3 and one for 10 because your dataset 2 has 4 rows.

Then with a formula tool you can compare the two amount and create the difference.

Note that in your formula tool, you have to use a round function to round each amount separately. That's because of how computers store numbers, as they might appear as 10.2 but in reality they might be stored as 10.1999999999999 and when you compute the difference that might cause you some discrepancies.

Hope that helps, let me know if that worked for you.

Regards,

Angelos

example.yxmd