Trying to have Graph Builder automatically plot data from Filtered Data Table Query

[#1]

Hello all,

JMP 16 user here. I have an add-in that pulls data from a work database and creates a table. In the particular file I'm interested in, there's data going back 20 years, but I only want data from the last 6 months.

I managed to build a query that filters the data from the source table and puts it into a new subset table. The query filters based on any date range I put in with the filter being set to prompt for an input whenever the script is run as in the image below.

The query does it's job and gives me the data from the range of dates I'm interested in. I've managed to get to script working as follows - input from source database -> query source table and filter based on date range -> open up a new table with new data.

What I then want is to plot the new data in Graph builder. I would like for this plot to automatically show up and populate whenever I run the script with the data from the range of dates I chose. I've tried copying the graph builder script to the ChemicalUsageQuery as in the above image, but when I run it, it either doesn't populate, or populates with data from the original source database table and the dates are change to 1904.

I would like for the script to run, prompt me for a date range, open up a new subset table, and then automatically graph the data.  Any help would be much appreciated.

Thanks,

[#2 · Accepted solution]

I think you just need to save a reference to the new table when you open it and then send the graph builder script to that reference. Just running Graph Builder() will run it against the current data table, which might not be the one you want.  For example note the role of dt2 in this script:

Names default to here(1);

dt1 = Open("$Sample_data/big class.jmp");

dt2 = New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		Associative Array({{dt1 << Get Name, "_MEMORY_"}})
	),
	QueryName( "SQLQuery1" ),
	Select(
		Column(
			"height",
			"t1",
			Alias( "Average-height" ),
			SavedJMPName( "height" ),
			Aggregation( "Average" )
		),
		Column(
			"age",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		)
	),
	From( Table( "big class", Alias( "t1" ) ) ),
	Group By(
		Column(
			"age",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		)
	)
) << Run;

dt2 << Graph Builder(
	Show Control Panel( 0 ),
	Variables( X( :age ), Y( :"Average-height"n ) ),
	Elements( Points( X, Y, Legend( 3 ) ) )
);

[#3]

Thank you so much. It worked.

[#4]

Hello again,

I've been trying to add some functionality to the above script. I currently have a text box that opens and prompts the user for dates and a check list box that prompts for the chemicals they want to see. This is so that the script only plots data for the chemical selected by the user for a given date range.

The data table called "dt" opens. What I want is to get the user selected rows located in list "lt" and put them into a new data table called "dt2". I would like dt2 to be updated for each iteration of the loop with the new data corresponding to lt[i].

For ( i = 1, i <= N Items(lt), i++,

	dt << Select Where(:chemical == lt[i] );
	
	dt2 = New Table("mytest", dt << Get Selected Rows);
	
);

The script above selects the rows, but opens up an empty dt2. 

Any help is appreciated. Thanks!

[#5]

You might try using subset:

Names default to here(1);

dt1 = Open("$Sample_data/big class.jmp");

//By selecting rows:
dt1 << Select Where(:age == 12);
dt2 = dt1 << Subset(
	Selected Rows( 1 ),
	Selected columns only( 0 )
);

//Or, by row numbers:
rowstokeep = dt1 << Get Rows Where(:age == 12);
dt3 = dt1 << Subset(
	Selected Rows( 0 ),
	Rows( rowstokeep ),
	Selected columns only( 0 )
);