844055_en-US

liliconcom

Building Runtime Data Table Formulas with JSL

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?

 

Re: Building Runtime Data Table Formulas with JSL

Would something like this work? It doesn't use the row() function, but also doesn't accomplish this exactly how you're trying to. I stole it from this page (which I have bookmarked for whenever I need to do eval/expr type work.

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

buildFormula = Function( {colRef, mult},
	Eval(
		Eval Expr(
			tbl << New Column( "New Formula Column", Formula( as column (expr (colRef)) * expr (num(mult)) ) )
		)
	)
);

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

Re: Building Runtime Data Table Formulas with JSL

Thanks Jed,

This is helpful, as is the link. I should have been clearer in the description of the problem. The formula evaluation can't be part of the function. I plan on using the formula expression in other parts of the code so I don't want it to evaluate immediately.

Re: Building Runtime Data Table Formulas with JSL

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

Re: Building Runtime Data Table Formulas with JSL

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

Re: Building Runtime Data Table Formulas with JSL

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)

 </CODE></PRE>
<PRE><CODE class=" language-jsl">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)
));<BR />