Subscribe Bookmark RSS Feed

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

floydfoxfb

Community Trekker

Joined:

Mar 27, 2016

All,

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

I have a data table as shown below

11457_pastedImage_13.png

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

7 REPLIES
vince_faller

Super User

Joined:

Mar 17, 2015

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. 

floydfoxfb

Community Trekker

Joined:

Mar 27, 2016

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

Nate_Riordan

Staff

Joined:

Sep 10, 2015

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 ) );

);

floydfoxfb

Community Trekker

Joined:

Mar 27, 2016

Nate,

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

Floyd

Wendy_Murphrey

Joined:

Jun 23, 2011

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
Nate_Riordan

Staff

Joined:

Sep 10, 2015

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.

11477_Screen Shot 2016-05-05 at 10.53.17 AM.png


Happy coding!

vince_faller

Super User

Joined:

Mar 17, 2015

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.