Building Runtime Data Table Formulas with JSL

Question

I run across this issue time to time and haven't come up with a solution I'm happy with, so I'm wondering what other folks have done. Assume the column name and additional formula values are not known until runtime. As a simple example, let's assume I want to create a formula that multiplies a column by a user supplied value.

This doesn't work:

buildFormula = Function({tblRef,colName,mult},
	exprOut = Substitute(
		Expr(col*mult),
		Expr(col),Column(tblRef,colName),
		Expr(mult),mult
	);
	Name Expr(exprOut);
);

outExpr = buildFormula(tbl,"Minimum Price ($1000)",3);

Eval(Substitute(
	Expr(tbl << New Column("New Formula Column",Formula(form))),
	Expr(form),Name Expr(outExpr)
));

Nor does this:

tbl = Open("$sample_data/cars 1993.JMP");

buildFormula = Function({colRef,mult},
	exprOut = Substitute(
		Expr(col*mult),
		Expr(col),colRef,
		Expr(mult),mult
	);
	Name Expr(exprOut);
);

outExpr = buildFormula(Column(tbl,"Minimum Price ($1000)"),Name Expr(fExpr));

Eval(Substitute(
	Expr(tbl << New Column("New Formula Column",Formula(form))),
	Expr(form),Name Expr(outExpr)
));

even though the appear to work if you go into the formula (until you click Evaluate). Am I missing something? I've been using this approach

tbl = Open("$sample_data/cars 1993.JMP");

buildFormula = Function({colRef,mult},
	exprOut = Substitute(
		Expr(col[Row()]*mult),
		Expr(col),colRef,
		Expr(mult),mult
	);
	Name Expr(exprOut);
);

outExpr = buildFormula(Column(tbl,"Minimum Price ($1000)"),3);

Eval(Substitute(
	Expr(tbl << New Column("New Formula Column",Formula(form))),
	Expr(form),Name Expr(outExpr)
));

But don't like having to use Row() to get the formula to evaluate properly. Is the behavior in the first two scripts a bug?

DonMcCormack · Answer

Years ago when talking to @JosephMorgan about this sort of thing, he strongly discouraged the use of string concatenation or substitution and indicated that everything could be accomplished though expression handling with the exception of one thing that I can't remember and may no longer be an issue. Using strings makes the code much more difficult to read and maintain. There are probably other more fundamental reasons but I can't recall. As it turns out, there is a simple solution using the As Scoped() function (thanks to @brady_brady)

tbl = Open("$sample_data/cars 1993.JMP");

buildFormula = Function({colRef,mult},
	exprOut = Substitute(
		Expr(col*mult),
		Expr(col),Name Expr(As Scoped(colRef)),
		Expr(mult),mult
	);
	Name Expr(exprOut);
);

outExpr = buildFormula(Column(tbl,"Minimum Price ($1000)"),3);

Eval(Substitute(
	Expr(tbl << New Column("New Formula Column",Formula(form))),
	Expr(form),Name Expr(outExpr)
));

scott_allen · Answer

Looks like I took a similar path to Mark. You can construct the formula as a string and then eval insert() and eval( parse() ) when you are ready to apply to the column. Would this work for your script?

tbl = Open("$sample_data/cars 1993.JMP");
colName = "Minimum Price ($1000)";
mult = 3;

formulaString = ":\!"^colName^\!"n * ^mult^";
formula = eval insert( formulaString );

newColumnString = "^tbl^ << new column( \!"New Formula\!", formula( ^formula^ ) )";
newColumn = eval insert( newColumnString );
eval( parse( newColumn ) );

Mark_Bailey · Answer

Here is an excerpt of some code I wrote for Chris. I have to build a formula from other formulas and make sure it supports the Profiler. Using an empty subscript on references helped but caused other problems. I am not certain what you want to do, but I hope this snippet provides some good ideas you can use.

For( r = 1, r <= nResponses, r++,

// convert a column reference to a column name for substitution

Col Ref to Name = Function( {colref, qualified? = 0 },
		If( qualified?,
			Parse(Eval Insert("^colref << Get Data Table^:\!"^colref << Get Name^\!"n")),
			Parse(Eval Insert(":\!"^colref << Get Name^\!"n"))
		)
	);

// create expected improvement column formula

limits = Column( dt, y col[r] ) << Get Property( "Response Limits" );
	goal[r] = Char( limits["Goal"] );
	Match( goal[r],

"Maximize",	// apply standard EI formula
			Eval(
				Substitute(
					Expr(
						ei = dt << New Column( y col[r] || " Expected Improvement", "Numeric", "Continuous",
							Formula(
								(:pppfff - yyy) * Normal Distribution( (:pppfff - yyy) / Sqrt( :vvvfff ))
								+ Sqrt( :vvvfff ) * Normal Density( (:pppfff - yyy) / Sqrt( :vvvfff ) )
							)
						)
					),
					Expr( vvvfff ), Col Ref To Name(vf),
					Expr( pppfff ), Col Ref To Name(pf),
					Expr( yyy ), refY[r] = predOptY[r]
				)
			),

"Minimize",	// negate standard EI formula
			Eval(
				Substitute(
					Expr(
						ei = dt << New Column( y col[r] || " Expected Improvement", "Numeric", "Continuous",
							Formula(
								(yyy - :pppfff) * Normal Distribution( (yyy - :pppfff) / Sqrt( :vvvfff ))
								+ Sqrt( :vvvfff ) * Normal Density( (yyy - :pppfff) / Sqrt( :vvvfff ) )
							)						)
					),
					Expr( vvvfff ), Col Ref To Name(vf),
					Expr( pppfff ), Col Ref To Name(pf),
					Expr( yyy ), refY[r] = predOptY[r]
				)
			),

"Match Target",	// apply joint EI formula
			Eval(
				Substitute(
					Expr(
						ei = dt << New Column( y col[r] || " Expected Improvement", "Numeric", "Continuous",
							Formula(
								deltaMin = Abs( ttt - yyy );
								deltaMin * (Normal Distribution( (deltaMin + ttt - :pppfff) / Sqrt( :vvvfff ) )
									- Normal Distribution( (-deltaMin + ttt - :pppfff) / Sqrt( :vvvfff ) )
								)
								+ Sqrt( :vvvfff ) * (
									Normal Density( (deltaMin + ttt - :pppfff) / Sqrt( :vvvfff ) )
									+ Normal Density( (-deltaMin + ttt - :pppfff) / Sqrt( :vvvfff ) )
									- 2 * Normal Density( ttt - :pppfff) / Sqrt( :vvvfff )
								)
								- (:pppfff - ttt) * (
									(Normal Distribution( (deltaMin + ttt - :pppfff)) / Sqrt( :vvvfff ) )
									+ Normal Distribution( (-deltaMin + ttt - :pppfff) / Sqrt( :vvvfff ) )
									- 2 * Normal Distribution( (ttt - :pppfff) / Sqrt( :vvvfff )  )
								)

)
						)
					),
					Expr( vvvfff ), Col Ref To Name(vf),
					Expr( pppfff ), Col Ref To Name(pf),
					Expr( ttt ),
					target[r] = If( Contains( Char( limits ), "Middle" ),
						limits["Middle"],
						(limits["Lower"] + limits["Upper"]) / 2
					),
					Expr( yyy ),
					/*
					yDiff = (Column( dt, y col ) << Get as Matrix) - target[r];
					pDiff = (pf << Get As Matrix) - target[r];
					refY[r] = If( Min( Abs( yDiff ) ) < Min( Abs( pDiff ) ),
						Column( dt, y col )[Loc Min( Abs( yDiff ) )],
						pf[Loc Min( Abs( pDiff ) )]
					)
					*/
					predOptY[r]
				)
			),

"None",	// unexpected condition
			Throw( "Disallowed: response with Goal of None" ),

// unexpected condition

gp << Close Window;
			dt << Delete Columns( { pf, vf } );
			Throw( "Disallowed: No response goal found. Response Limits column property is missing" )
	);

ei	<< Set Property( "Response Limits", {Goal( Maximize ), Importance( 1 ), Show Limits( 0 )} )
		<< Hide( 1 );
	Wait( 0 );

Eval(
		Substitute(
			Expr(
				Insert Into( utilityCols, eee, iii );
			),
			Expr( eee ), Eval( ei ),
			Expr( iii ), 3*(r-1) + 1
		)
	);
);