cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
UserID16644
Level V

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

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 ModelQty
06/24/2023A23
06/30/2023A25
07/01/2023A34
07/05/2023A50

 

Table dtB

Date ModelRef
06/01/2023A100
07/01/2023A150

 

After updating dtA, the table should look like this

Date ModelQtyRef
06/24/2023A23100
06/30/2023A25100
07/01/2023A34150
07/05/2023A50 

 

Is this possible? Please help

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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

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

View solution in original post

7 REPLIES 7
txnelson
Super User

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

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

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
UserID16644
Level V

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

Hi txnelson, it is prompting an error like this. 

UserID16644_0-1695969793610.png

 

jthi
Super User

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

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

-Jarmo
txnelson
Super User

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

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.

Jim
UserID16644
Level V

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

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 ModelQty
06/24/2023A23
06/30/2023A25
07/01/2023A34
07/05/2023A50
07/10/2023A65
07/27/2023A68

 

Table dtB

Date ModelRef
06/01/2023A100
07/01/2023A150
07/20/2023A300
08/01/2023A250

 

After updating dtA, the table should look like this

Date ModelQtyRef
06/24/2023A23100
06/30/2023A25100
07/01/2023A34150
07/05/2023A50300
07/10/2023A65300
07/27/2023A65250

 

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 ModelQtyRef
06/24/2023A23250
06/30/2023A25250
07/01/2023A34250
07/05/2023A50250
07/10/2023A65250
07/27/2023A65250
txnelson
Super User

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

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] = .
		)
	)
);
Jim
jthi
Super User

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

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)

jthi_0-1695965556973.png

Concatenate the tables

jthi_1-1695965577506.png

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

jthi_2-1695965644215.png

Select all rows which have QTY missing and delete those

jthi_3-1695965669190.png

jthi_4-1695965680563.png

 

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

-Jarmo