## Manipulating Summary Tables and Column Addition

Contributor

Joined:

Jun 9, 2017

Hi,

I have two quesitons:

1) How could I go about summing the values of a row between two columns. I found the snipet of code below, but it simply copies the 5th value and puts in in the new row. For each row, I want all values between the 5th row and the nth column of TR to be added.

TotRow = dt << Get Column Names( Numeric );
TR = N Items( TotRow );
dt << New Column( "Total", Numeric );
For Each Row( :Total[] = (Sum( (:(Column( dt, TotRow[5 :: TR] ))) )) );

2) Secondly, how do you mainpulate summary tables. I'm attempting to sort the values in a summary table and then add a column (in that order); however, when I run the script it opens mulitple summary tables, a basic one, and one with the new columns, nor does it perform the two opeartions in th correct order. I just want the modified one and I want to be able to reference it to further add to it. Code shown below:

dt2 = dt << Summary( Group( :Name, :Final ), Link to original data table( 0 ) );

dt2 << Sort( By( :Final ), Order( Descending ) );
dt2 << New Column( "Description", Character, Values( {"a", "b", "c"} ) );

Thanks! I appreciate the help this discussion board has been giving me.

Super User

Joined:

Jun 22, 2012

Here is a script that answers both of your questions.  You need to start using

Help==>Scripting Index

to get the complete list of options for all functions and platforms

Names Default To Here( 1 );
dt = Open( "\$SAMPLE_DATA\big class.jmp" );
// First way
dt << New Column( "HW Sum", formula( :Height + :Weight ) );

// Second Way
dt << New Column( "Create with Loop" );
For Each Row( :Create with Loop = :Height + :Weight );

// Second Question.....use the Replace Table option on the sort
dt2 = dt << Summary( Group( :Sex ), Link to original data table( 0 ) );

dt2 << Sort( By( :Sex ), Order( Descending ), Replace table( 1 ) );
dt2 << New Column( "Description", Character, Values( {"a", "b", "c"} ) );
Jim