Approximate match for numerical values with Threshold

I am trying to do an approximate match between two sheets.




In the Paypal sheet - I want to put the date if the transaction amount was found in myob sheet.


As sometimes the amount differs by +/-0.01 MYOB dates could not be found using a joiner or vlookup. In excel this problem can be solved using an approximate match but again you cannot define a threshold like 0.05 cents.

Is there a way I can define a threshold and then try to match these table in KNIME?

I am uploading my sample data below.
Sample.xlsx (19.3 KB)

The only possible way I could think of doing this was using the maths function and add 0.01 cents and then rematch and join.

Thanks in advance

This is not optimized - I just threw it together quickly - but try and see if it works for you. There is almost certainly a better way to do it. :slight_smile:

The basic idea is:

  • Exact match on Date and Gross where you can
  • Where you don’t have matches, define some range for the Gross ( I used +/- 0.05) and try to match again (using very inefficient cross join, so this won’t scale well probably)
  • Combine original matches and best guess matches into single table.

RangedMatchingExample.knwf (30.0 KB)

1 Like

Thanks a lot for the solution. I have understood the logic who I can do this. I will try and tweak this now.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.