Column Formula

[#1]

A question from aandw about column formulas started this post. If you only use the formula editor to create column formulas, you can ignore almost all of this. This post is about using JSL to create a column formula.

First, a little bit about the Formula command. Here's a really simple one:

The formula for column C adds columns A and B

The table script (top left triangle->copy table script, paste into an editor) looks like this:

New Table( "abc", Add Rows( 2 ),  
  New Column( "a", Numeric, "Continuous",Format( "Best", 12 ),Set Values( [1, 7] )),  
  New Column( "b",Numeric,"Continuous",Format( "Best", 12 ),Set Values( [2, 9] )),  
  New Column( "c", Numeric, "Continuous", Format( "Best", 12 ),  
  Formula( :a + :b )  
  )  
)  

Line 5 sets the formula; the formula is an expression that adds columns a and b. The expression does not run until much later. The expression is saved, as an expression, as part of the column. If another row is added to the table or if the values in columns a or b are changed, the expression can be evaluated again to get a value for column c.

This is both friendly and unusual behavior. Unusual because most functions evaluate their arguments: sqrt( 4 + 5 ) returns 3. Friendly because you would almost never want JMP to evaluate a column's formula before it was assigned to the column.

Sometimes a formula expression might be really complicated. So complicated it would be easier to write a program to write the expression. To do that in the formula command, you'd need a way to tell the formula command to pre-evaluate the argument and use the resulting expression as the formula. You do that by adding an eval( ) wrapper around the code to pre-evaluate. When the formula command sees "eval" wrapped around the expression, then it runs the code and uses the result.

lookup = function({t,u}, expr(a+b) );  
New Table( "abc",  
  Add Rows( 2 ),  
  New Column( "a",Numeric,"Continuous",Format( "Best", 12 ),Set Values( [1, 7] )),  
  New Column( "b",Numeric,"Continuous",Format( "Best", 12 ),Set Values( [2, 9] )),  
  New Column( "c", Numeric, "Continuous", Format( "Best", 12 ),  
  Formula( eval( lookup(1,2) ) )  
  )  
) 

The user-defined "lookup" function (line 1) isn't very bright, but it has to be executed to retrieve expr(a+b) as the returned value. On line 7, the eval() wrapper means the column formula will not be set to "lookup(1,2)", but instead will be set to the expression returned by lookup(1,2).  Same table as before, with same column formula.

What if you wanted the column formula to be set to an expression that begins with eval( ) ?  How could the formula command do that, since starting with eval( ) means evaluate the argument and use the result? (I'd have been hard pressed to come up with an interesting example, but MS has one in the question's answer.) The answer is Name Expr( ). Name Expr is a JSL function that is normally used to get an expression out of a JSL variable without evaluating it. As the outer-most wrapper in a formula command, it just returns the argument without evaluating it. The first example could use it, for no good reason except this explanation, like this, to return exactly the same table:

New Table( "abc", Add Rows( 2 ),  
  New Column( "a", Numeric, "Continuous",Format( "Best", 12 ),Set Values( [1, 7] )),  
  New Column( "b",Numeric,"Continuous",Format( "Best", 12 ),Set Values( [2, 9] )),  
  New Column( "c", Numeric, "Continuous", Format( "Best", 12 ),  
  Formula( Name Expr( :a + :b ) )  
  )  
)  

But for MS example, it is needed because of the clever use of eval (which probably wants another blog post to explain what it does). Here's the final table from the original question. I added my own idea as a second formula column.

New Table( "final?",Add Rows( 9 ),  
  New Column( "color",Character,"Nominal",  
    Set Values({"green", "green", "blue", "yellow", "red", "red", "yellow", "green", "green"})),  
  New Column( "count",Numeric,"Continuous",Format( "Best", 12 ),  
    Formula( Name Expr( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :color ) ) ) ) )),  
  New Column( "another count",Numeric,"Continuous",Format( "Best", 12 ),  
    Formula(  
      n = 0;  
      For( i = 1, i <= Row(), i++,  
        If( :color == :color[i], n++ )  
      );  
      n;  
    )  
  )  
);

The third column's formula could be faster, at the expense of clarity, by making it look backward for the last match and adding one to that count. Clarity vs efficiency is a trade-off.

So, I'm off to fix the bug discovered in this process: When a data table creates a script to reproduce itself (like the top example), it has to produce JSL to reproduce the column formulas too. In the case where the column formula begins with the special eval( ) wrapper, and the eval is supposed to be part of the formula, JMP needs to add the Name Expr.

update 21Apr2018: repair the automated conversion from the previous blog.