Subscribe Bookmark RSS Feed

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

dank77

Community Member

Joined:

Nov 16, 2015

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

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

);

3 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

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

Solution

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

);