Create a subset based on date filtering

js1985 — Wed, 22 Apr 2020 12:46:05 GMT

Hello all,

I have a JMP data table on which I want to create a script to create a subset based on a timeframe I want to keep flexible.

Looking on the community I found the following piece of script (reported here below) which is good for me, I'm only missing the part of code where, after selecting the start date and end date the subset is created in a new datatable.

Someone can help me?

Thanks.

dt = current data table ();

tmpstr =
"SELECT pen_parametric.pn_id, pen_parametric.equiplk_ky, parametric_dlk.paramlk_ky,
FROM MATS.parametric_dlk parametric_dlk, MATS.pen_parametric pen_parametric
WHERE parametric_dlk.paramlk_ky = parametric_ref_llk.paramlk_ky 
  AND parametric_dlk.paramlk_ky = pen_parametric.paramlk_ky 
   AND ((pen_parametric.part_dm Between {ts '^sdt^'} And {ts '^edt^'}))";

New Window( "Query Date",
	<<Modal, 
	sd = Today();
	ed = Today();
	H List Box(
		Text Box( "From:" ),
		scal = Number Edit Box( sd, <<Set Format( Format( "yyyy-mm-dd" ) ), <<SetFunction( Function( {this}, sd = scal << Get ) ), <<Set Show Spin Box(1) ),
		Spacer Box( Size( 20, 20 ) ),
		Text Box( "To:" ),
		ecal = Number Edit Box( ed, <<Set Format( Format( "yyyy-mm-dd" ) ), <<SetFunction( Function( {this}, ed = ecal << Get ) ), <<Set Show Spin Box(1) ),
	);
);

sdt = Munger( Format Date( sd, "yyyy-mm-ddThh:mm:ss" ), 1, "T", " " );
edt = Munger( Format Date( ed, "yyyy-mm-ddThh:mm:ss" ), 1, "T", " " ); 


Show( sdt, edt );
 
qstring = Eval Insert( tmpstr );
Show( qstring )

Re: Create a subset based on date filtering

Phil_Kay — Fri, 24 Apr 2020 10:21:49 GMT

So the script that you have will result in a defined start date, "sdt", and a defined end date, "edt".

All you should need is a script to select rows in the table where the date is between sdt and edt.

Then subset the selected rows.

Take a look in the JMP Scripting Index. Help > Scripting Index. And look at the information and examples for Select Where and Subset.

Here is an example from the index for Select Where:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Quality Control/Diameter.jmp" );
dt << Process Screening(
	Y( :DIAMETER ),
	Grouping( :MACHINE, :Phase ),
	Select Where( Alarm Rate > 0 )
);

Here is an example from the index for Subset.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Subset(
	Rows(
		[28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40]
	)
);

If you just want a script that will give you a subset table from the selected rows in a table, here is an example using the Big Class sample data set:

Data Table( "Big Class" ) << Subset(
	Selected Rows( 1 ),
)

Open the Big Class data set from the sample data in JMP. Select some rows manually. Then run this script and see what happens.

Hopefully all this helps.

Regards,

Phil

topic Re: Create a subset based on date filtering in Discussions

Create a subset based on date filtering

Re: Create a subset based on date filtering