join data based on approximate time stamp

Question

I have two data tables that i need to join.  table A has time stamps in hours: 24, 48, 72, 96,....  table B has online data with 5 minute intervals, so the time stamp must have several decimal places to capture the precision: 1.000, 1.083, 1.167, 1.250, 1.333, 1.417, 1.500 etc.  joining B to A works well. these is no problems because there is always a whole number match of time stamp in table B with table A. so for all of A's time points there is a corresponding value in B. the problem is that some times table A ends in "120.1" or "120.4" hours for example. This means that the join fails and no data from B will join to A at the final data point. is there a way to get "approximate" matches like the way VLOOKUP works in excel? I cannot simple round the values of table B because then i have repeating values (at the 0.3 and 0.8 intervals)

Phil_Kay · Answer

Another idea that you might wish to check is using JMP Query Builder to do "fuzzy" joins.

I looked at this for someone that wanted to identify chemical components that are within a certain range of mass from mass spectrometry.

I have attached 3 tables as an example. The Joined table has class from the Ref table where Mass in the Data table is within the range of Mass Low and Mass High.

I am happy to provide more guidance if you think this solution would be useful.

This is how the fuzzy join is defined in JMP Query Builder:

Joined table.jmp

Example Ref Table.jmp

Example Data Table.jmp

brady_brady · Answer

HI,

Check out the Loc Sorted( ) function. It can be used to perform lookups.

For example, the code below assigns letter grades to simulated test scores. That is, using a lookup matrix [0, 60, 70, 80, 90], sample scores of 85, 67, 99, 92 and 89 are assigned letter grades F, D, C, B and A depending if the score exceeds 0, 60, 70, 80 or 90, respectively. I've used it in ways analogous to your use case.

Cheers,

Brady

Words( "FDCBA", "" )[Loc Sorted( [0, 60, 70, 80, 90], [85, 67, 99, 92, 89] )];