462354_en-US

liliconcom

Add fuzzy join / asof merge / join by columns which do not have exact matches

Allow joining two data tables based on not exact matches. I come across situations where I have to be able to join two databases (tables) together based on timestamp which aren't exact matches. This would allow me to do it based on those timestamps. The joins could also be based on string-similarities but I haven't (yet) come across situations where I would need to do that.

 

Continuous column join should let user to define tolerance and allow using grouping column(s).

 

Below are some example packages, which can provide some ideas on what type of joins and options would be helpful:

• Simpler package could be similar to Pandas - merge_asof .
• More complicated join options could be found from R - fuzzyjoin: Join data frames on inexact matching which allows joining based on multiple different matching types (string similarity, regex, numeric values within tolerance)

There are couple of scripts found from JMP Community which can do this (or parts of this), but it would be helpful to have this possibility directly built in JMP:

• "Fuzzymerge" - join tables with slightly different timestamps by @klk 
• Join Nearest Rows - Join tables by using continuous columns and nearest values by @jthi (me)

Data AccessData Blending and CleanupRe: Add fuzzy join / asof merge / join by columns which do not have exact matches

Please also consider "fuzzy merge" in higher dimensions, like in this post:
How do I add data from one table to another table based on the distance 

Re: Add fuzzy join / asof merge / join by columns which do not have exact matches

besides "no matching entry", there is another reason for fuzzy joins:

non-matching entries because of how floating-point data is stored in JMP (and other programs):
Issues with Update() on floating-point EFT values