Use expr() to replace variable column names in a formula

[#1]

I try to create nested loops:

1st loop create some columns and have formula with it

2nd loop create some other columns, and the formula will need columns from 1st loop

My question is in 2nd loop, I cannot correctly setup the column names in formula by using eval() + substitue() + expr()

Names Default To Here( 1 );
dt = current data table();
a=3;
b=3; 
alist = {1,2,3}; 
blist = {1,2,3};
// In actual script, above numbers will come from H list box input

For (i=1, i <= a, i++, 
			dt << New Column( eval("a_" || char(alist[i])), Formula(:gap*2) );
                        

	col = "a_" || char(alist[i]);

	For (k=1, k <= b, k++, 
			eval(
				substitute(
				Expr(
				dt << New Column( eval("a_" || char(alist[i]) || " b_" || char(blist[k])), Formula( If(:vvv > 0 & (:vvv < blist[k] ),1,0) ) );
					),
				Expr(vvv),
				col  //here gives wrong result, the formula doesn't really use column a_1, a_2, a_3, instead it use "a_1"......etc
				)
			)
		)
	)

[#2 · Accepted solution]

This might give some idea

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

a = 3;
b = 3;
alist = {1, 2, 3};
blist = {1, 2, 3};
// In actual script, above numbers will come from H list box input

For(i = 1, i <= a, i++,
	new_col = dt << New Column(Eval("a_" || Char(alist[i])), Formula(:height* 2));
	
	For(k = 1, k <= b, k++,
		Eval(Substitute(
			Expr(dt << New Column("a_" || char(new_col << get name) || " b_" || char(blist[k]),
				Formula(If(vvv > 0 & (vvv < www), 1, 0))
			)),
			Expr(vvv), Name Expr(AsColumn(new_col)),
			Expr(www), blist[k]
			)
		)
	);
);

Edit:

If you have JMP16+ you could change the loops to For Each (this also uses Eval(EvalExpr()) instead of Eval(Substitute()) as in my opinion it is more clear in simple cases like this

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

alist = {1, 2, 3};
blist = {1, 2, 3};

For Each({aval}, alist,
	new_col = dt << New Column("a_" || Char(aval), Formula(:height * 2));

	For Each({bval}, blist,
		col_title = "a_" || Char(new_col << get name) || " b_" || Char(bval);
		
		Eval(EvalExpr(
			dt << New Column(col_title, Numeric, Nominal, Formula(
				If(0 < Expr(Name Expr(As Column(dt, new_col))) <  Expr(bval),
					1
				,
					0
				)
			))
		));
	);
);

[#3]

This resolved my question, thank you. And the second method looks even more clear

One more question, I use blist[k] directly in my code, and you use "www" then replace it. Both give same calculated result

Is it because my method will leave a variable  blist[k] in the formula that can go wrong if user try to apply formula again after the code has executed?

[#4]

Short answer:

Yes. If you lose reference to either blist or k, formula cannot re-evaluate. And if formulas are re-evaluated while you still have access to both of those, same k will be used for all of those formulas which would yield incorrect answers. You should be able to test this by running dt << Rerun formulas;

View more...

Working

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

alist = {1, 2, 3};
blist = {1, 120, 999};

For Each({aval}, alist,
	new_col = dt << New Column("a_" || Char(aval), Formula(:height * 2));

	For Each({bval}, blist,
		col_title = "a_" || Char(new_col << get name) || " b_" || Char(bval);
		
		Eval(EvalExpr(
			dt << New Column(col_title, Numeric, Nominal, Formula(
				If(0 < Expr(Name Expr(As Column(dt, new_col))) <  Expr(bval),
					1
				,
					0
				)
			))
		));
	);
);

dt << rerun formulas;

Possible issues (I changed to For loop as it is a bit easier to use for demonstration in this case)

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

alist = {1, 2, 3};
blist = {1, 120, 999};

For Each({aval}, alist,
	new_col = dt << New Column("a_" || Char(aval), Formula(:height * 2));

	For(i = 1, i <= N Items(blist), i++,
		col_title = "a_" || Char(new_col << get name) || " b_" || Char(blist[i]);
		
		Eval(EvalExpr(
			dt << New Column(col_title, Numeric, Nominal, Formula(
				If(0 < Expr(Name Expr(As Column(dt, new_col))) <  blist[i],
					1
				,
					0
				)
			))
		));
	);
);
i = 3; // i would be 4 otherwise, so I set it to 3 for demo purposes
dt << rerun formulas;

Long answer:

Yes, if you don't evaluate them, it will leave blist[k] into your data table. This would then be last k value you have in all of the formulas (issue if formulas are re-evaluated while still having access to blist and k) and if the table is reopened (or for some reason you lose access to blist / k) those won't exist at all. These types of cases can be usually easily verified by checking the formula

other place where this can easily happen is graphic scripts and you can similarly see them from the graphic script window

Names Default To Here(1);

dt = open("$SAMPLE_DATA/Big Class.jmp");

gb = dt << Graph Builder(
	Variables(X(:weight), Y(:height), Overlay(:sex)),
	Elements(Points(X, Y, Legend(9)), Line Of Fit(X, Y, Legend(11)))
);

line = 60;
framebox = Report(gb)[frame box(1)];
framebox << Add Graphics Script(
	H Line(line)
);

line = 70;
framebox << Add Graphics Script(
	H Line(line)
);

You will have two scripts like this

and there will be no line on Y = 60. Also if you copy the graph builder script it will have line there instead of values

Graph Builder(
	Variables(X(:weight), Y(:height), Overlay(:sex)),
	Elements(Points(X, Y, Legend(9)), Line Of Fit(X, Y, Legend(11))),
	SendToReport(
		Dispatch(
			{},
			"Graph Builder",
			FrameBox,
			{Add Graphics Script(9, Description(""), H Line(line)),
			Add Graphics Script(10, Description(""), H Line(line))}
		)
	)
)

and easy fix for this is to evaluate the values

Names Default To Here(1);

dt = open("$SAMPLE_DATA/Big Class.jmp");

gb = dt << Graph Builder(
	Variables(X(:weight), Y(:height), Overlay(:sex)),
	Elements(Points(X, Y, Legend(9)), Line Of Fit(X, Y, Legend(11)))
);

line = 60;
framebox = Report(gb)[frame box(1)];
Eval(EvalExpr(
	framebox << Add Graphics Script(
		H Line(Expr(line))
	);
));

line = 70;
Eval(EvalExpr(
	framebox << Add Graphics Script(
		H Line(Expr(line))
	);
));

In more general, I would say that if you start replacing variable names by evaluating them to values, it might be a sometimes a good idea to evaluate all of them. What I mean by this in this case is that it might be beneficial to evaluate the column title also

Eval(EvalExpr(
	dt << New Column(Expr(col_title), Numeric, Nominal, Formula(
		If(0 < Expr(Name Expr(As Column(dt, new_col))) <  Expr(bval),
			1
		,
			0
		)
	))
));
);

This has for example additional benefit when you are debugging your code. Below is simple example of creating column from reference and with a variable which does work fine

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

new_col = dt << New Column("Test", Numeric, Continuous, << Set Each Value(1));

col_title = (new_col << get name) || "* var";
var = 2;

Eval(EvalExpr(
	dt << New Column(col_title, Numeric, Nominal, Formula(
		Expr(NameExpr(AsColumn(dt, new_col))) * Expr(var)
	));
));

but if you had some issues, you could just highlight the EvalExpr() and everything inside it and run that part of the script

col_title isn't evaluated (not a problem in this case BUT it could be in if you had more complicated script) and just by adding (in this case) one more Expr() you will get more information what is really going on

[#5]

Thank you for such detailed information, and I can understand your point, I'll modified my code as your example