topic Re: JMP 16: Script to add columns with formula, using relative reference in Discussions

JMP 16: Script to add columns with formula, using relative reference

Rakendu — Sat, 10 Jun 2023 23:37:48 GMT

Hi,

Trying to write my first ever script in JMP, would greatly appreciate some help.

I have 10 columns (lets say one, two, three, four, five, six , seven, eight, nine, ten) and a Total column.

and i need to add ten more column which calculates the percentage of the first ten columns. I cannot use column names because i have 150 columns actually and the column names are all different.

How do i get it to add a new column and everytime advance the column in the formula by 1

For example :

create new column col 11

fill with formula "col 1 / Total"

set name of column to 'pct_one'

create new column col 12

fill with formula "col 2 / Total"

set name of column to 'pct_two'

continue for 10 columns.

Thanks in advance!

Re: JMP 16: Script to add columns with formula, using relative reference

Mark_Bailey — Mon, 04 Oct 2021 20:20:19 GMT

See this example:

Names Default to Here( 1 );

// mock up user data table
dt = New Table( "Mock Up" );
For( c = 1, c < 6, c++,
	col = dt << New Column( "My Column " || Char( c ), Numeric, Continuous );
	col << Set Values( J( 25, 1, Random Normal( 80, 5 ) ) );
);
y = dt << Get As Matrix;
col = dt << New Column( "Total", Numeric, Continuous );
col << Set Values( y * J( 5, 1, 1 ) );

// solution
dt = Current Data Table();
total = Column( N Col( dt ) ) << Get As Matrix;
last c = N Col( dt );
For( c = 1, c < last c,  c++,
	dt << New Column( "Relative " || (Column( c ) << Get Name), Numeric, Continuous,
		Values( (Column( c ) << Get Values) :\ total )
	);
);

Re: JMP 16: Script to add columns with formula, using relative reference

Rakendu — Tue, 05 Oct 2021 01:15:24 GMT

Thanks Mark! Worked like a charm!

Did some tweaks, leaving it here for any future reference. Thanks alot!

Names Default to Here( 1 );

// mock up user data table
dt = New Table( "Mock Up" );
For( c = 1, c < 6, c++,
	col = dt << New Column( "My Column " || Char( c ), Numeric, Continuous );
	col << Set Values( J( 25, 1, Random Normal( 80, 5 ) ) );
);
y = dt << Get As Matrix;
col = dt << New Column( "Total", Numeric, Continuous );
col << Set Values( y * J( 5, 1, 1 ) );

// solution
dt = Current Data Table();
total = Column( N Col( dt ) ) << Get As Matrix;
last c = N Col( dt );
For( c = 1, c < last c,  c++,
	dt << New Column( "Relative " || (Column( c ) << Get Name), Numeric, Continuous,
		Formula( :As Column( c ) /:Total )
	);
);