Set column formula dynamically with JSL

[#1]

I'm working on a JSL script where I need to dynamically build a formula for a given column inside a loop. The idea is to reference a different column for each iteration, so that the formula uses the evaluated column name (e.g., :col_name) rather than inserting the literal code.

Here's a simplified version of what I'm trying to achieve in the loop (I have tried many variations of Expr, Eval, Parse in this code to no avail):

all_cols = dt << Get Column Names();
col = Column(all_cols[N Items(all_cols)-1]); // Changes for every i in the loop

// Dynamically build the formula expression using column names directly
formula = If(
    col > :UL_outliers | col < :LL_outliers,
    1,
    0
);

outlier_col << Set Formula(formula);

The problem is that when I set the formula using Set Formula(), instead of dynamically evaluating the column reference, the formula is inserted literally into the column's formula editor. As a result, the column formula is not evaluated and is simply: 

If(
    col > :UL_outliers | col < :LL_outliers,
    1,
    0
);

I have read Dynamically-created-formula-not-evaluating and some other related posts, but my mind is melting. I just want some 1's or 0's in my outlier column.

Any help or workaround would be greatly appreciated. Thanks in advance!

[#2 · Accepted solution]

Not 100% sure what you are trying to do but this might give some ideas

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");
dt << new column("LL_outliers", Numeric, Continuous, Set Each Value(50));
dt << new column("UL_outliers", Numeric, Continuous, Set Each Value(70));

all_cols = dt << Get Column Names("Continuous", "String");

colname = all_cols[1];
f_expr = Substitute(
	Expr(If(_col_ > :UL_outliers | _col_ < :LL_outliers,
		1,
		0
	)),
	Expr(_col_), Name Expr(AsColumn(dt, colname));
);

new_col = dt << New Column("F", Numeric, Nominal);
Eval(EvalExpr(
	new_col << Set Formula(Expr(NameExpr(f_expr)));
));

[#3 · Accepted solution]

JMP has a string data type:

a = "cat";

b = "3 + 5";

a and b are strings. B looks like an expression, but is not.

c = parse( b );

c is an expression, which can be shown as a tree:

   operator(+)
     /      \
   3         5

c = expr( 3 + 5 ); // is another way to get the same expression stored in c

d = eval( c ); // or just d = c; the right hand side is evaluated anyway

d is 8.

Sometimes JMP will automatically evaluate c to 8, sometimes not. Sometimes you want 8, sometimes you want 3+5.

e = nameexpr(c);

e gets the expression that c holds; think of nameexpr as a thin wrapper; when the wrapper is evaluated it blocks a deeper evaluation and returns the 3+5 expression, unevaluated, rather than 8.

JMP also has a set of functions for manipulating an expression tree, not covered here. @jthi  used substitute, for example.

I think part of what you are puzzling over is the difference between the way different functions in JSL will handle an expression argument:

cos(c) , like most functions, will evaluate c to 8. This is what you expect.

colname<<setformula( c ) literally copies the expression (c, not 3+5, not 8 ) to the column formula without evaluating it. (Just as it would for  <<setformula(col1 + col2), which is what you want. Not like the cos() function.)

So setformula has two special cases:

colname<<setformula( c )    --> c  (not special)

colname<<setformula( eval(c) )  --> 8   (eval wrapper)

colname<<setformula( nameexpr(c) )   --> 3+5   (nameexpr wrapper)

Note that these are indeed special cases; setformula makes an extra effort to detect the eval or nameexpr wrapper to make the JSL do what you expect.

Now that I'm nearly done I wish I'd used x+y rather than 3+8 for part of this; it would be more clear why you might want to postpone the evaluation to a later point in time. Also, even a single number like 8 is an expression; eval(8) is 8.

[#4]

Thank you for the mindbending but very educational explanation. I think I understand it at least conceptually.

Guess I'll find out.

 Thanks a lot !