Set column formula dynamically with JSL

Question

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!

jthi · Accepted Answer

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

Craige_Hales · Accepted Answer

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< c (not special) colname< 8 (eval wrapper) colname< 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.