Update table according to the date less than or equal to the reference file

[#1]

Hi all, 

I have 2 tables where the dtA is my original file and dtB is my reference file. What I would wanted to do is to have dtA be updated according to the date less than or equal to the reference file.

For example, 

Table dtA

Date	Model	Qty
06/24/2023	A	23
06/30/2023	A	25
07/01/2023	A	34
07/05/2023	A	50

Table dtB

Date	Model	Ref
06/01/2023	A	100
07/01/2023	A	150

After updating dtA, the table should look like this

Date	Model	Qty	Ref
06/24/2023	A	23	100
06/30/2023	A	25	100
07/01/2023	A	34	150
07/05/2023	A	50

Is this possible? Please help

[#2 · Accepted solution]

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

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] )
	)]
);

[#3]

Hi txnelson, it is prompting an error like this. 

[#4]

You are most likely running older JMP version than 16 (For Each was added in JMP16). You can fairly easily modify it and change it into For loop

[#5]

I suspect that you have a version of JMP that does not support 

For Each()

so if you change the For Each section to

For(row=1, row<= N Rows( dtA ), row++,
	dtA:Ref[row] = dtB:ref[Max(
		dtB << get rows where( dtB:date <= dtA:date[row] )
	)]
);

it should work.

[#6]

Script is working fine now, but I have one last question, is there a way to limit the update to a certain date since the tables will be updated everyday? For example,  

Table dtA

Date	Model	Qty
06/24/2023	A	23
06/30/2023	A	25
07/01/2023	A	34
07/05/2023	A	50
07/10/2023	A	65
07/27/2023	A	68

Table dtB

Date	Model	Ref
06/01/2023	A	100
07/01/2023	A	150
07/20/2023	A	300
08/01/2023	A	250

After updating dtA, the table should look like this

Date	Model	Qty	Ref
06/24/2023	A	23	100
06/30/2023	A	25	100
07/01/2023	A	34	150
07/05/2023	A	50	300
07/10/2023	A	65	300
07/27/2023	A	65	250

I tried using the script, but it overwrites the past dates Ref value with the newest one which is 250. It looks like this

Date	Model	Qty	Ref
06/24/2023	A	23	250
06/30/2023	A	25	250
07/01/2023	A	34	250
07/05/2023	A	50	250
07/10/2023	A	65	250
07/27/2023	A	65	250

[#7]

The purpose of the Discussion Community with regard to help with scripts, , is not to be an on demand script generation source.  It is here to help individual get past issues with scripts they are developing, and to increase their ability to enhance their own scripts.  

If you have not taken the time to read the Scripting Guide, I strongly suggest that you do that.  It will show you the details of the scripting language and you will see from that, that JSL is a full scripting language that can handle virtually anything you can throw at it.  However, concerning your current question, you can easily place conditional "IF()" statements to define when the update should be executed or not executed.  Below is an illustration of such

For( row = 1, row <= N Rows( dtA ), row++,
	If( dtb:Date == Today(),
		If( dtA:date[row] <= Col Max( dtB:date ),
			dtA:Ref[row] = dtB:ref[Max(
				dtB << get rows where( dtB:date <= dtA:date[row] )
			)],
			dtA:Ref[row] = .
		)
	)
);

[#8]

There are few different methods of doing this, but JMP doesn't (at least yet) offer such a join directly. 

First you can create Ref to your original table (which will be empty) (this step is most likely unnecessary as JMP will add missing columns when concatenating)

Concatenate the tables

Sort by groups and time. Select all the values in Ref column and right click to select Fill missing

Select all rows which have QTY missing and delete those

There are also some addins (and scripts) that can do this join in community. Also you can use SQL within JMP