Join based on tolerace level

[Sshasnk]

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	currency	amount
1234	USD	10.20
1238	USD	10.10
2671	EUR	45.20

Dataset 2:

Account	currency	amount
1234	USD	10.30
1234	USD	10.00
2671	EUR	45.20
1238	USD	10.40

Output:

Account	currency	amount	Result
1234	USD	10.20	Present
1238	USD	10.10	Not present
2671	EUR	45.20	Present

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

[AngelosPachis]

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

[Qiu]

@Sshasnk 
Hope this helps

[SeanAdams]

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.