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

[#1]

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

[#2]

You'd start by generating your table variables:

dt << New Table Variable("RMSE_1", 30);

You'd probably do that as part of your loop before you generate the new formula column.  

After that, a table variable is referenced similarly to columns so from above:

// Add the table variable
dt << New Table Variable( "RMSE_1", 30 );

// Add the column referencing the table variable
dt << New Column( "Column 1",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :RMSE_1 ^ 2 )
);

best,

M

[#3]

The problem is that I cannot hardcode the table variable names in the column formula since the number of columns as well as the names of those columns is unknown. Creating the table variables is doable. Referencing them in the column formula is the hard part.

[#4]

Oh, I see. Since you're generating the column names on the fly there are a couple of strategies you could use.

The first one is to just use a couple of "substitute into()" commands on a list of column names you generate at run time. This will generally work well in most cases.

Secondly, you could use the Eval(Parse(<string>)) method, where you build up a string of your JSL programmatically using concatenate commands. This is my silver bullet. Particularly with stuff related to column properties and manipulation. It removes the need for all the extra Eval() commands because the script is basically "typing" the finished command as a string and converting it to an expression. You then just run the expression.

[#5]

I agree with the silver bullet method, but it is not pretty. It does work though :)

[#6]

I'm still looking for a more robust solution since the parse(eval()) method doesn't deal nicely with () in the name of the table variable or column name.

[#7]

The Eval(Parse()) method can easily handle complex column names.  You just need to recognize that such issues might exist. Here is a simple example

Names Default to here(1);
dt = New Table( "big class",
	Add Rows( 40 ),
	New Column(
		"Complex column a+7/(1_2)",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values(
			[59, 61, 55, 66, 52, 60, 61, 51, 60, 61, 56, 65, 63, 58, 59, 61, 62, 65, 63,
			62, 63, 64, 65, 64, 68, 64, 69, 62, 64, 67, 65, 66, 62, 66, 65, 60, 68, 62,
			68, 70]
		)
	),
	New Column(
		"weight",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 5, 0 ),
		Set Values(
			[95, 123, 74, 145, 64, 84, 128, 79, 112, 107, 67, 98, 105, 95, 79, 81, 91,
			142, 84, 85, 93, 99, 119, 92, 112, 99, 113, 92, 112, 128, 111, 105, 104,
			106, 112, 115, 128, 116, 134, 172]
		)
	)
);

col names = dt<<get column names(string);

Eval( Parse( "Bivariate( Y( :Name( \!"" || col names[1] || "\!" ) ), X( :Name( \!"" || col names[2] || "\!" )  ) );" ) );

[#8 · Accepted solution]

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