nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to set a formula on many columns in a elegant way

dank77

Hi Guys,

Can someone help me with the below question?

I would like to avoid long formula as below: (each element is a column name!)

Column("Std")<<Add Column Properties(

Formula(

Std Dev(

:F23 m A,:F23 m C,:F23 m H,:F23 M A,:F23 M C,:F23 M H,:F23 N A,:F23 N C,:F23 N H,

:F30 m A,:F30 m C,:F30 m H,:F30 M A,:F30 M C,:F30 M H,:F30 N A,:F30 N C,:F30 N H,

:F36 m A,:F36 m C,:F36 m H,:F36 M A,:F36 M C,:F36 M H,:F36 N A,:F36 N C,:F36 N H,

:F37 m A,:F37 m C,:F37 m H,:F37 M A,:F37 M C,:F37 M H,:F37 N A,:F37 N C, :F37 N H,

:F49 m A,:F49 m C,:F49 m H,:F49 M A,:F49 M C,:F49 M H,:F49 N A,:F49 N C, :F49 N H,

:F53 m A,:F53 m C,:F53 m H,:F53 M A,:F53 M C,:F53 M H,:F53 N A,:F53 N C, :F53 N H,

:F87 m A,:F87 m C,:F87 m H,:F87 M A,:F87 M C,:F87 M H,:F87 N A,:F87 N C, :F87 N H)));

Cheers,

Dan

Accepted answers

Both above solutions will work, but be aware that for Ron's solution the formula may work only until the global variable colnames is redefined or cleared, e.g. when the JMP session is closed.

Here is some code comparing the two approaches:

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

colnames = dt << get column names;

// Ron's approach: Variables in formula not evalueated

dt << New Column("Std", Formula(Std Dev(colnames[4 :: 5])));

// Ian's approach: Variables evaluated before setting formula

formula = Eval Expr(Std Dev(Expr(colnames[4 :: 5])));

dt << New Column("Std2", Formula(Name Expr(Formula)));

// Compare formulas in the log

Print(:Std << get formula, Std2 << get formula);

//Clear variable definitions and add rows. Only Std2 will recognize changes

Clear Globals();

Current Data Table() << concatenate(

Open("$SAMPLE_DATA/Students2.jmp"),

append to first table

);

All comments

ian_jmp

This code 'avoids' in the sense that it uses the currently selected columns so you don't have to type. Is that what you are after, please?

Do 'File > New > New Script', cut and paste the JSL, then do 'Edit > Run Script'.

NamesDefaultToHere(1);

// Make a table

nc = 30;

dt = NewTable("Test");

for (c=1, c<=nc, c++,

dt << NewColumn("Col"||Char(c), Numeric, Continuous, Formula(RandomNormal()));

);

dt << AddRows(100);

// Select half the columns

n = Round(nc/2);

c = RandomIndex(NCol(dt),n);

for (i=1, i<=n, i++,

Column(dt, c[i]) << setSelected(1);

);

// Run this code to make a new formula column for the standard deviatiion of the columns currently selected in a table

selCols = dt << getSelectedColumns;

addCol = Expr(dt << NewColumn("StdDev", Numeric, Continuous, Formula(StdDev(colsTBD))));

SubstituteInto(addCol, Expr(colsTBD), Eval(selCols));

addCol;

ron_horne

hi dank77,

Ian@JMP's solution is correct. Yet, if you are looking for something less rigorous you may try the following.

assuming the columns you need for the formula are all together in a range you can introduce the number of the first and last as follows:

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

colnames = dt << get column names;

col = dt << new column ("Std");

col << Set Formula( std dev (colnames[4::5] ));

best,

ron

Both above solutions will work, but be aware that for Ron's solution the formula may work only until the global variable colnames is redefined or cleared, e.g. when the JMP session is closed.

Here is some code comparing the two approaches:

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

colnames = dt << get column names;

// Ron's approach: Variables in formula not evalueated

dt << New Column("Std", Formula(Std Dev(colnames[4 :: 5])));

// Ian's approach: Variables evaluated before setting formula

formula = Eval Expr(Std Dev(Expr(colnames[4 :: 5])));

dt << New Column("Std2", Formula(Name Expr(Formula)));

// Compare formulas in the log

Print(:Std << get formula, Std2 << get formula);

//Clear variable definitions and add rows. Only Std2 will recognize changes

Clear Globals();

Current Data Table() << concatenate(

Open("$SAMPLE_DATA/Students2.jmp"),

append to first table

);