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.
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)
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.