JSL: how to create a reference to a table variable inside a column formula

pauldeen

How do I insert a reference to a dynamically generated table variable name inside a column formula such that I can later change the table variable and still have the column values update. And then do this for a unknown number of columns.

 

 

dt = Current Data Table();

NewCol = function({ColNr},
	//Set a name table variable with the name of the column and RMSE appended to it
	dt << Set Table Variable( concat(Column(ColNr)<< Get Name(),"RMSE"), 0 );
	
	//Create a new column with the same name as the original column but with " with RMSE" at the end.
	//Place a formula in this column which takes the original column value and add random RMSE as specified by the table variable to it.
	Eval(
		substitute(
			expr(
				dt<<New Column(Concat(__ResponseCol__<< Get Name()," with RMSE"),Numeric,
				"Nominal",
				Format( "Best", 12 ),
				Formula(__ResponseCol__ + Random Normal(0,__RMSE__)),
				)
			),
		Expr(__ResponseCol__),Column(ColNr),
		Expr(__RMSE__),/*How to create a reference to the table variable here*/,
		)
	)
);

//Should later iterate from 7 to 9 and scale with the numer of response columns in the data table.
//Hardcoded to 7 for this example.
NewCol(7);

 

 

Example.jmp

pauldeen

Thank you for challenging me, I gave it one more try and solved it

You are right, creating the column name works fine. It does not work for creating the formula part of the new column expression.

What I had to do is put :Name( "...") around the column and table variable names in the parse function.

 

dt = New Table( "Untitled 9",
	Add Rows( 0 ),
	New Column( "Pred Formula Thickness (A)",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[-1.09815926721176, -0.143977108758327, -1.60078929360768,
			-0.340409665497429, -0.581215173360104, 0.233876291958488,
			-0.357108531367114, 0.0704280592629584, 1.86236889119533,
			0.669812241478143, -0.00962200322380528, 1.33041117732878,
			0.533367791752501, 2.24183743599671, -0.506060621538248]
		),
		Set Display Width( 145 )
	)
);
ColNr = 1;

//Create the table variable based on the column name with RMSE added to it.
dt << Set Table Variable( concat(column(ColNr)<< Get Name()," RMSE"), 0 );

//Create the text for the column formula. Notice that the formula stored in test is correct. It is the 'column name' + 'the table variable' multiplied with random normal.
test = ":Name( \!"" || char(column(ColNr)<< Get Name()) || "\!") + :Name( \!"" || char(column(ColNr)<< Get Name()) || " RMSE\!") * Random Normal()";

//But when putting this in a column formula equation, the parser stops parsing after the second repeat of the column name. In an ideal world I would just refer to the table variable directly.
eval(parse( "dt<<New Column(\!"" || char(column(ColNr)<< Get Name()) || " with RMSE\!",
		Numeric,
		\!"Continous\!",
		Format( \!"Best\!", 12 ),
		Formula(" || test || "),
	)"	
));