Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
zjuv007
Level III

Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

For instance, I'd like to prompt the user at a particular point in the script to have them select the product numbers that are represented in this particular row to filter on and have JMP create a new table based off of this input.

 

Thank you,

Zach

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ian_jmp
Staff

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

Building on @markbailey's approach, here's a little more (old) code:

NamesDefaultToHere(1);

// Make a table with a list of 'products'
dt = NewTable("Products", NewColumn("Product ID", Character, Nominal, Formula(Char(RandomInteger(1, 10)))));
dt << addRows(20);
dt << runFormulas;

// Put up a list of distinct products that occur in 'dt'
prods = AssociativeArray(Column(dt, "Product ID") << getValues) << getKeys;
nw = NewWindow("Pick some Products from the List", 
					lb = ListBox(prods), 
					ButtonBox("OK", OKscript)
					);

// This script runs when the user hits 'OK'
OKscript =
Expr(
	nw << closeWindow;
	// Find out which products were selected
	selecedProds = lb << getSelected;
	// Find out all corresponding rows in 'dt'
	selectedRows = [];
	for(p=1, p<=NItems(selecedProds), p++,
		selectedRows = VConcat(selectedRows, dt << getRowsWhere(:Product ID == selecedProds[p]))
		);
	// Make the subset table
	dt2 = dt  << Subset(Rows(selectedRows), LinkToOriginalDataTable(1));
);

View solution in original post

13 REPLIES 13
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

You can probably use the JMP Query Builder to create this workflow automatically. Use the column of product numbers as your Filter column and choose the Prompt on Run option.

 

JMPScreenSnapz015.png

You can then go to the red triangle and choose Copy Run Script and paste that script into your script to create the subset table as a part of your script.

JMPScreenSnapz016.png

-Jeff
Highlighted

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

This is 'bare bones' but it should get you started.

 

Names Default to Here( 1 );

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

Summarize( age group = By( :age ) );
// you could also use an associative array to get the unique levels.

New Window( "Selection", << Modal,
	lb = List Box(
		age group,
	),
	Button Box( "OK",
		got em = lb << Get Selected;
	)
);

For( g = 1, g <= N Items( got em), g++,
	// do something with each selected group
	Show( got em[g] );
);

 

 

Learn it once, use it forever!
Highlighted
ian_jmp
Staff

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

Building on @markbailey's approach, here's a little more (old) code:

NamesDefaultToHere(1);

// Make a table with a list of 'products'
dt = NewTable("Products", NewColumn("Product ID", Character, Nominal, Formula(Char(RandomInteger(1, 10)))));
dt << addRows(20);
dt << runFormulas;

// Put up a list of distinct products that occur in 'dt'
prods = AssociativeArray(Column(dt, "Product ID") << getValues) << getKeys;
nw = NewWindow("Pick some Products from the List", 
					lb = ListBox(prods), 
					ButtonBox("OK", OKscript)
					);

// This script runs when the user hits 'OK'
OKscript =
Expr(
	nw << closeWindow;
	// Find out which products were selected
	selecedProds = lb << getSelected;
	// Find out all corresponding rows in 'dt'
	selectedRows = [];
	for(p=1, p<=NItems(selecedProds), p++,
		selectedRows = VConcat(selectedRows, dt << getRowsWhere(:Product ID == selecedProds[p]))
		);
	// Make the subset table
	dt2 = dt  << Subset(Rows(selectedRows), LinkToOriginalDataTable(1));
);

View solution in original post

Highlighted
zjuv007
Level III

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

If I'm wanting to do this to an existing table, and correspondingly take ALL columns associated with this table as well, but only bring over the respective rows specific to the filtered Products, how can I simply do that?

Highlighted
zjuv007
Level III

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

Here is a snippet of this code, note that dt2 (PrimaryRFR_OUT_2015) is the data table i'm interested in filtering down by Product CFN, creating a new table, and saving this table. Note that the filter box shows up accurately with the correct Product CFN list, but once I hit OK I get "Invalid Row #":

 

dt2 << Save( "PrimaryRFR_OUT_2015.jmp" );

tab = dt2 << Tabulate(
	Add Table(
		Column Table( Grouping Columns( :Code LLT of OUT_2015 ) ),
		Row Table( Grouping Columns( :Code LLT of PrimaryRFR_2015, :Code LLT Desc of PrimaryRFR_2015) )
	)
);
dt_tab = tab << Make Into Data Table;
dt_tab << set name( "Tabulate" );
dt_tab << Save( "PrimaryRFR_OUT_Tabulate_2015.jmp" );

prods = AssociativeArray(Column(dt2, "Product CFN") << getValues) << getKeys;
nw = NewWindow("Pick Products from the List to be filtered",
	lb = ListBox(prods),
	ButtonBox("OK", OKscript)
	);
	
	OKscript =
	Expr(
		nw << closeWindow;
		SelectedProds = lb << getSelected;
		selectedRows = [];
		for( p = 1, p <= NItems( SelectedProds ), p++,
			selectedRows = VConcat( selectedRows, dt << getRowsWhere( :Product CFN == SelectedProds[p]))
			);
			dt5 = dt2 << Subset(Rows(selectedRows), LinkToOriginalDataTable(1));
	);

 

Highlighted
txnelson
Super User

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

In your line that starts with
selectedRows = VConcat( selectedRows……….
the reference to "dt" should be "dt2"
Jim
Highlighted
zjuv007
Level III

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

Sometimes you get so lost in your own coding you forget which way is up and which way is down. Thank you all! :)
Highlighted
txnelson
Super User

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

 I found the error, because I have made the same mistake many times before:-)

Jim
Highlighted
zjuv007
Level III

Re: Is there a way to ask the user to select what common product #'s in a column to filter for and then generate a report off that filtered selection?

One more shoot off, How do I save this file with the new product #'s now? It is not saving my ZACH_2015.jmp

//*** Create a window to prompt user to filter for Model numbers they would like to use
prods = AssociativeArray(Column( dt2, "Product CFN" ) << getValues) << getKeys;
nw = NewWindow( "Pick Products from the List to be filtered",
	lb = ListBox( prods ),
	ButtonBox( "OK", OKscript )
	);
	
	OKscript =
	Expr(
		nw << closeWindow;
		SelectedProds = lb << getSelected;
		selectedRows = [];
		for( p = 1, p <= NItems( SelectedProds ), p++,
			selectedRows = VConcat( selectedRows, dt2 << getRowsWhere( :Product CFN == SelectedProds[p]))
			);
			dt5 = dt2 << Subset(Rows(selectedRows), LinkToOriginalDataTable(1));
	);
			dt5 << Save( "ZACH_2015.jmp");
Article Labels

    There are no labels assigned to this post.