cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Glo_Roh
Level II

JSL Create new summary table with Max values of variable columns

Hello,

I've enjoyed reading and finding solutions on these discussion pages for a while now, but now have been fumbling to figure this out.

I have a data table that gives variable number of columns, auto generated by machine, where the first 14 columns are always the same identifying information. The other columns, 15 onwards, are Numeric Continuous data where the column names will always vary.

 

Issue is I am trying to automate the creation of a summary table that will group the data by it's identifying information and also have a column of the Max data for each data column.

With a varying number of data columns and varying column names, Is it possible to iterate through each column? Such as a for-loop to get all the Max values into the summary table? I am limited by being unable to name all the columns b/c of the varying column names each pull, but it is safe to say every column after the 14th column will need the Max values found.

 

This is the JSL script I've been trying to edit, but hit a dead end on generating the Max columns.

 

 

sumdt = table << Summary(
	Group(:NAME,:ID),
	(Max(Column(table, 15:154)), // having issue on this line
	Output Table Name("Summary Max Values")
);

 

Thank you for any inputs to my issue!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: JSL Create new summary table with Max values of variable columns

Here is an example of doing what you want with the Big Class sample data table.

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);

txnelson_0-1626827888177.png

 

Jim

View solution in original post

Glo_Roh
Level II

Re: JSL Create new summary table with Max values of variable columns

Thanks so much @txnelson 
That is perfect. Since I know which columns I want removed, the Remove from() function is just what I needed.

Here's basically the code that does exactly what I need:

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
Remove From(colNamesList, 1,2 ); //removes first two columns that I don't want evaluated	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);

View solution in original post

6 REPLIES 6
txnelson
Super User

Re: JSL Create new summary table with Max values of variable columns

Here is an example of doing what you want with the Big Class sample data table.

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);

txnelson_0-1626827888177.png

 

Jim
Glo_Roh
Level II

Re: JSL Create new summary table with Max values of variable columns

Thank you @txnelson ,

that partially solved my issue.

My original data table has date columns that I do not want Max values analyzed, basically running into issue that I don't want to be calculating the Max values of the first 14 columns regardless of their data type. 
Is there a way to ignore the first 14 columns or would it be easier create a new table that deletes the unwanted columns first before passing it through for a summary table?

 

Thanks,

Gloria

txnelson
Super User

Re: JSL Create new summary table with Max values of variable columns

After the colNamesList is created, you can use the Remove From() function to delete specific rows in the list, and then once the list is whittled down to just the columns you want, you can use it for the generation of the Summary Table.

 

See the Scripting Index entry for the definition and example of the Remove From() function 

Jim
Glo_Roh
Level II

Re: JSL Create new summary table with Max values of variable columns

Thanks so much @txnelson 
That is perfect. Since I know which columns I want removed, the Remove from() function is just what I needed.

Here's basically the code that does exactly what I need:

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
Remove From(colNamesList, 1,2 ); //removes first two columns that I don't want evaluated	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);
GoodMan
Level III

Re: JSL Create new summary table with Max values of variable columns

Hi, Thanks for your code. Here i found i just can get Max of first numeric column, height. Please see attachment. I guess the Max just function to the first value of list. Can you help me know why? 

 

GoodMan
Level III

Re: JSL Create new summary table with Max values of variable columns

Under thw latest version JMP, the code works fine. Thanks!