topic Re: Script: Data Table Subset With Combo Box Filters in Discussions

Script: Data Table Subset With Combo Box Filters

FaxMachine — Mon, 22 May 2017 21:07:12 GMT

Hello,

I have been trying to write a script to create a subset of a larger data table. The new subset should contain a subject ID number, D.O.B, and a Range of Selected Dates. I wrote a script that asks for the ID#, start date, and end date in three different text boxes. (Included below, and it's far from perfect). However, I would like some drop down menus in the pop-up window as to show what ID#s are available for the current data table, and the accompanying dates for that subject. Following the example from here, http://www.jmp.com/support/notes/54/614.html, I have tried working with Combo Boxes, but can't seem to get it right.

Where the problem comes down to is making the second/third drop down menu react accordingly to the ID# selection. Things I have noticed: Summarize and Associative Array help reduce duplicate dates and white spaces, and dates are not in the prefered "m/d/y".

Any Help Appreciated!

dt = Current Data Table();

Column(dt, "Coll Date") << Data Type("Numeric", Informat("m/d/y"), Format("m/d/y") );
Column(dt, "Coll Date") << Modeling Type("Continuous");
Column(dt, "Coll Date") << Order(Ascending);

nw = New Window( "Subject ID by Date Range.",
	<<Modal,
	V List Box( Text Box( "Select an ID# and Date Range. " ), Spacer Box( Size( 25, 25 ) ) ),
	V List Box(
		Lineup Box(
			3,
			Text Box( "Subject ID: " ),
			sub = Number Edit Box( . ),
			Text Box( "Start Date: " ),
			start = Text Edit Box( . ),
			Text Box( "End Date: " ),
			end = Text Edit Box( . )
		),
		Spacer Box( Size( 25, 25 ) ),
		H List Box(
			Button Box( "Click to Update First",
				subid = sub << Get;
				startDate = start << Get Text;
				endDate = end << Get Text;
			)
		)
	)
);
For Each Row(
	Selected( Row State() ) = :Patient ID1 == subid & Format( :Coll Date, "m/d/y" )
	 >= Char( startDate ) & Format( :Coll Date, "m/d/y" ) <= Char( endDate )
);
subdt = dt << Subset(
	Columns( :Patient ID1, :DOB, :Coll Date ),
	Output Table Name( "subset" ), 
);
subdt<< sort(by(:Coll Date), Replace Table);

Re: Script: Data Table Subset With Combo Box Filters

FaxMachine — Mon, 22 May 2017 21:18:09 GMT

This is where I stand on using Combo Boxes, it's a big mess...

dt = Current Data Table();

Column( dt, "Coll Date" ) << Data Type( "Character", Informat( "d/m/y" ), Format( "m/d/y" ) );
Column( dt, "Coll Date" ) << Modeling Type( "Continuous" );
Column( dt, "Coll Date" ) << Order( Ascending );

Summarize( a = By( :Patient ID1 ) );
Insert Into( a, "<Select ID#>", 1 );
//Summarize( b = By( :Coll Date ) );
date = Associative Array( ( :Coll Date << Get Values ) ) << Get Keys;

New Window( "Example",
<<Modal,
H List Box(
Panel Box( "Select Patient ID: ",
cb1 = Combo Box(
a,
<<SetFunction(
Function( {this},
selection = this << Get Selected();
r = //For Each Row( Selected(Row State() = :Patient ID1 == Num(selection)));
dt << Get Rows Where( :Patient ID == Num( selection ) );
Summarize( b = By( :Coll Date[r] ) );
b << Data Type( "Character", Informat( "d/m/y" ), Format( "m/d/y" ) );
//b = Associative Array( (:Coll Date[r] << Get Values)) << Get Keys;
//startDate = Associative Array( (date[r] << Get Values ) ) << Get Keys;
cb2 << Set Items( date[r] );
)
)
)
),

Panel Box( "Select a Start Date: ",
cb2 = Combo Box(
b,
<<SetFunction(
Function( {this},
one = this << Get Selected();
g = dt << Get Rows Where( :Coll Date > one );
Summarize( c = By( :Coll Date[g] ) );
endDate = :Coll Date[g];
cb3 << Set Items( endDate );
)
)
)
),

Panel Box( "Select an End Date: ",
cb3 = Combo Box(
b,
<<SetFunction(
Function( {this},
two = this << Get Selected() ) )
)
)
)
);

For Each Row(
Selected( Row State() ) = :Patient ID1 == Char( Selection ) & Format( :Coll Date, "m/d/y" ) >= one
& Format( :Coll Date, "m/d/y" ) <= two
);

subdt = dt << Subset( Columns( :Patient ID1, :DOB, :Coll Date ), Output Table Name( "Subset" ), );

subdt << Sort( By( :Coll Date ), Replace Table );

Re: Script: Data Table Subset With Combo Box Filters

txnelson — Mon, 22 May 2017 23:26:55 GMT

You certainly can create your own unique dialog box, with the requirements you specified, but the simple way of doing what you want is to script a data fileter, then using the builtin selection capability, the user can then simply go to the red triangle and select "Show Subset" and the data table you wrote about will be created

dt=open("$SAMPLE_DATA\Movies.jmp");
Current Data Table() << Data Filter(
	Location( {37, 37} ),
	Add Filter(
		columns( :Type, :Year ),
		Display( :Type, Size( 160, 90 ), List Display ),
		Display( Size( 394, 16 ), :Year )
	)
);

Re: Script: Data Table Subset With Combo Box Filters

FaxMachine — Tue, 23 May 2017 00:34:36 GMT

oh wow, a lot simpler!

Following the samle data, selecting the movie Type is great, would it be possible to get something similar for the Year column. Maybe format the type to get discrete values as opposed to a year down to the third decimal place?

Re: Script: Data Table Subset With Combo Box Filters

txnelson — Tue, 23 May 2017 01:07:56 GMT

The selection device, pick list vs. slider is based upon what the modeling type for the column is. If you just change the modeling type to "Ordinal" it will make the selection a pick list.

dt=open("$SAMPLE_DATA\Movies.jmp");
dt:year << set modeling type(ordinal);
Current Data Table() << Data Filter(
	Location( {37, 37} ),
	Add Filter(
		columns( :Type, :Year ),
		Display( :Type, Size( 160, 90 ), List Display ),
		Display( Size( 394, 16 ), :Year )
	)
);

or, you could turn the Year column into an actual JMP Date value, and place a date format on the column, and you will see a much nicer slider version of the code

dt=open("$SAMPLE_DATA\Movies.jmp");
for each row(
dt:year= date mdy(1,1,year));
dt:year << format("m/d/y");
Current Data Table() << Data Filter(
	Location( {37, 37} ),
	Add Filter(
		columns( :Type, :Year ),
		Display( :Type, Size( 160, 90 ), List Display ),
		Display( Size( 394, 16 ), :Year )
	)
);

Re: Script: Data Table Subset With Combo Box Filters

Tinkerbell — Tue, 29 Sep 2020 22:23:04 GMT

How do you script showing the subset table part?