Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Re: How to set a formula on many columns in a elegant way

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 16, 2015 12:39 AM
(4561 views)

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

Solved Go to Solution

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**;