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

[#1]

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

[#2]

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. 

[#3]

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

[#4]

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

[#5]

Nate,

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

Floyd

[#6 · Accepted solution]

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

[#7]

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!

[#8]

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.