Issues with Update() on floating-point EFT values

[#1]

I am merging sample-level metadata with time-series raw measurements using Python’s merge_asof and then updating the time-series table in JMP using Update(). Both tables contain columns for time (EFT) and batch ID.

The problem:

• After the Python merge, the resulting table (merged_samples) contains exact matches for some time points and “nearest” matches for others.

Python merge handles “nearest” correctly:

merged_batch = pd.merge_asof(
    metadata_batch.sort_values('EFT_val'),
    timeseries_batch.sort_values('EFT_val'),
    left_on='EFT_val',
    right_on='EFT_val',
    direction='nearest'
)

• Python correctly pairs 208.0955556 (raw) with 209 (metadata).

• The resulting merged_batch has all sample rows matched to nearest time-series points.

JMP Update() fails for close values :

timeSeriesTable << Update(
    With( merged_batch ),
    Match Columns(
        :EFT_val = :EFT_val,
        :Batch_ID = :Batch_ID
    )
);

1. Metadata has fewer rows than the time-series table.

2. Even values that look identical in the UI fail  fail due to floating-point precision differences.

When I plot the graph in 'merged_batch'  individual table it works fine but after the update  in `timeSeriesTable `, the graph doesn't plot all the data point and some rows are missed too. 

Like this: 

0 - present
87.9669444444445 - present
111.966666666667 - missing
135.966111111111 - missing
183.965555555556 - present
205.965277777778 - present

[#2]

Can you provide example tables? Are you trying to join 208.0955556 with 209 in JMP?

JMP doesn't have join nearest natively (you can script it and there are scripts in community / were add-ins for it). There is also at least one wish list item requesting it.

[#3]

Yes, since JMP doesn't have that capability I used python which works and integrated very well with the code. And I am attaching the tables for reference. 

and No, I am not  trying to join 208.0955556 with 209.  209 is recorded value and the float  is from machine, that's why I am using the mergeasof in python for nearest values. 

[#4]

Is there some issue or this just "heads-up" that JMP doesn't have this capability?

[#5]

This - When I plot the graph in 'merged_batch'  individual table it works fine but after the update  in `timeSeriesTable `, the graph doesn't plot all the data point and some rows are missed too.

[#6]

"111.966666666667 - missing"

Do we have all the required tables to create the plot? Should we assume meta-data is same as merged_batch?

For me, join works but I really wouldn't trust it as performing exact join with floating points might give you results you are not expecting as they tend to not to be exactly precise (Floating Point Math).

You can try giving your numbers more decimals to more accurately see what JMP thinks they are

[#7]

Join works for me as well, and though both of these columns have similar timestamps as I'm filtering from raw-data itself but quiet unsire about JMP's Update Behaviour. 

The reason I am not using Join is it will end up creating subsequent column names 'column name ' of 'Table name' 'n' , where 'n' is the subsequent table number from multiple runs and I faced that hurdle and moved to Update Table. 

So better way it to use a fixed decimal ? 1e-8, 1e-12 ? 

dt = Current Data Table();

col = Column( dt, "EFT" );

// Replace each value with a rounded version (e.g., 8 decimals)
col << Set Each Value( Round( :EFT, 1e-14 ) );

[#8]

I used Join to demonstrate as using Update with the provided data doesn't do anything, but join does offer merge same name columns (and update main table with data from second table).

I think rounding might be one option. Or could you use pandas to build the final table as you are already using it earlier? Also, as a heads-up you might not want to use << Set Each Value in that way, as I'm not sure if JMP has yet changed it to be the intended behaviour.

[#9]

This feature is pending for a while - please support @jthi 's wish and vote: 

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

[#10]

AddIn: Join Nearest Rows - Join tables by using continuous columns and nearest values  / @jthi