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
- :
- How to use column values as input for a formula in a script?

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

Highlighted

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

May 4, 2016 9:14 AM
(7733 views)

All,

I beleive my question is very basic but I have yet to find an answer.

I have a data table as shown below

I wish to

a) include 3 new columns labelled %CFB column 3, %CFB column 4 and %CFB column 5. %CFB stand for % change from baseline.

b) Into each new column I wish to enter the % change from baseline with values derived from the following formula

%CFB = (x - Column 3)/Column 3, where "x" will be replaced with values from Column 3, 4 or 5 on each iteration coded below. Column 3 is the baseline value

```
dt = Current Data Table();
startcol = 3;
endcol = 5;
For( i = startcol, i <= endcol, i++,
data_col = Column( i ) << get name;
New column ("%CFB " || data_col) << Formula((( :data_col - :column 3) / :column 3) * 100 ));
```

Using the script above I have been able to create the new columns but no results from the formula are placed in them. When I manually swap **column 3** for data_col in the formula all new columns are evaluated as zero as I would expect. This tells me the formula is at least correct and the data_col variable is not being interpreted/substituted as I would like. Any insight into getting this to work would be appreciated.

Thanks in advance for your assistance

Floyd Fox

1 ACCEPTED SOLUTION

Accepted Solutions

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

Hello,

With this example, the variable data_col still appears in the formula. Since you are assigning data_col in a For loop, any attempts to evaluate the formulas after the loop finishes will produce the same values in all three new columns. This is because data_col will represent the last value from the For loop, "Column 5". In addition, suppose you save the table and then close JMP. When you open the table again in a new instance of JMP and attempt to evaluate the formula, an error will appear because JMP no longer knows what data_col should represent.

When you use a variable in a formula, you will need to do something to instruct JMP to replace the variable with its value before evaluating the formula. You can use Substitute(), but I find that the Eval Expr() method (as vince.faller0 mentioned) is easier for most people to implement. Eval Expr() tells JMP to evaluate (replace) any variables that are wrapped in an Expr() function within the expression you supply as the argument. An outer Eval() will cause JMP to evaluate the entire New Column statement after the replacement has occurred. JMP Usage Note 41824 also demonstrates this concept.

I hope this helps.

Wendy

Wendy

7 REPLIES 7

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

Re: How to use column values as input for a formula in a script?

You can't do :variable (especially of a string, which is what <<Get name will give you).

If you change it to

```
data_col = Column( i ) ;
Eval(EvalExpr(New column ("%CFB " || Expr(data_col<<Get Name), Formula((( Expr(data_col) - :column 3) / :column 3) * 100 ))));
```

it should work.

It just substitutes all of the Expr()s inside EvalExpr() with their actual values instead of variables.

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

Re: How to use column values as input for a formula in a script?

Vince,

Thank you for this. I frankly will have to do some reading about the expression you provided but I do appreciate your insight into why my code did not work.

Regards

Floyd

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

Re: How to use column values as input for a formula in a script?

vince.faller0 has indeed accurately identified the root of the issue. There is no column named data_col in the data table. Hence the code as written is referring to something that doesn't exist. Also, Vince is showing some of the powerful ways of using JSL to write the code to hold dynamic references. That said, in any good language you will have multiple options to execute logic.

I'd suggest using an alternate format of referencing the column. While the simple format is to use the semicolon and the column name to reference the current data table columns a more powerful way is to use the column(data table reference, column string or number) format. Below I've modified your code slightly to use that format.

```
dt = Current Data Table();
startcol = 3;
endcol = 5;
For( i = startcol, i <= endcol, i++,
data_col = Column( i ) << get name;
col_name = "%CFB " || data_col;
dt << New Column(col_name,
Numeric,
Continuous,
Formula( ((column(dt, data_col)[] - :column 3) / :column 3) * 100 ) );
);
```

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

Re: How to use column values as input for a formula in a script?

Nate,

Thanks this code works and has the added benefit that I can understand the logic behind the code.

Floyd

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

Hello,

With this example, the variable data_col still appears in the formula. Since you are assigning data_col in a For loop, any attempts to evaluate the formulas after the loop finishes will produce the same values in all three new columns. This is because data_col will represent the last value from the For loop, "Column 5". In addition, suppose you save the table and then close JMP. When you open the table again in a new instance of JMP and attempt to evaluate the formula, an error will appear because JMP no longer knows what data_col should represent.

When you use a variable in a formula, you will need to do something to instruct JMP to replace the variable with its value before evaluating the formula. You can use Substitute(), but I find that the Eval Expr() method (as vince.faller0 mentioned) is easier for most people to implement. Eval Expr() tells JMP to evaluate (replace) any variables that are wrapped in an Expr() function within the expression you supply as the argument. An outer Eval() will cause JMP to evaluate the entire New Column statement after the replacement has occurred. JMP Usage Note 41824 also demonstrates this concept.

I hope this helps.

Wendy

Wendy

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

Re: How to use column values as input for a formula in a script?

Excellent note about dealing with scoping there WK Wrapping it all together for floydfoxfb with some code formatting.

```
For( i = startcol, i <= endcol, i++,
data_col = Column( i ) << get name;
new_col_name = "%CFB " || data_col;
Eval(
Eval Expr(
dt << New Column( Expr(new_col_name),
Numeric,
Continuous,
Formula( ((Column( dt, Expr( data_col ) )[] - :column 3) / :column 3) * 100 )
)
)
);
);
```

Another workaround to the variable scoping would be to utilize Set Each Value( equation ) instead of Formula( equation). This has the benefit of using less resources as a formula is not being computed, but it has the drawback of a loss in modifying said formula.

As with anything good there are many ways to approach a problem using the right tools. As you mentioned a Substitute() is a great option as well when things start getting more advanced. Substitute is especially useful in combination with Parse() which turns strings into JSL code and Eval() which then allows the JSL to be executed, this leads to Eval(Parse(Substitute())) statements. This final technique is particularly useful when wrapping functionality around built in JMP platforms. To further illustrate the combination of parse() and eval(parse) I've included a simple example.

Happy coding!

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

Re: How to use column values as input for a formula in a script?

Don't know how much opinions are welcome, but here goes.

Personally I'm a huge opponent to Eval(Parse()), just because it drastically reduces readability inside the editor. I've seen 5000 line scripts that are, for the most part, entirely pink (or whatever color you choose for your strings). With the ability to substitute expressions, you keep your syntax highlighting intact and I think just make it much easier to edit other people's script.

I still very occasionally use eval(parse()), I try to use JSL quote() though when I have to.