Here is a script that works. It will not be very efficient if the tables bet real large.
Given your 2 data tables the script will provide the update
![txnelson_0-1695961483960.png txnelson_0-1695961483960.png](https://community.jmp.com/t5/image/serverpage/image-id/57085iECA728728693FD20/image-size/medium?v=v2&px=400)
Please note that row 4 has an update value, where your table did not have one. Unless I am misinterpreting your requirement, the last row has a value in the reference table that meets te selection criteria
Names Default To Here( 1 );
New Table( "Original",
Add Rows( 4 ),
New Column( "Date",
Format( "m/d/y", 10 ),
Input Format( "m/d/y" ),
Set Values( [3770409600, 3770928000, 3771014400, 3771360000] )
),
New Column( "Model", Character( 16 ), Set Values( {"A", "A", "A", "A"} ) ),
New Column( "Qty", Set Values( [23, 25, 34, 50] ) )
);
New Table( "Reference",
Add Rows( 2 ),
New Column( "Date",
Numeric,
"Continuous",
Format( "m/d/y", 10 ),
Input Format( "m/d/y" ),
Set Values( [3768422400, 3771014400] )
),
New Column( "Model", Character( 16 ), Set Values( {"A", "A"} ) ),
New Column( "Ref", Set Values( [100, 150] ) )
);
// This is the code that does the actual update
dtA = Data Table( "Original" );
dtB = Data Table( "Reference" );
dtA << New Column( "Ref" );
For Each( {row}, Index( 1, N Rows( dtA ) ),
dtA:Ref[row] = dtB:ref[Max(
dtB << get rows where( dtB:date <= dtA:date[row] )
)]
);
Jim