JSL: How to pass a variable to a column formula

[#1]

Dear All,

  I'm having some trouble trying to create a new column that is a formula and sending it a variable within my JSL script.

  I have a date table where I'm trying to calculate the average lag of a column. The column of interest isn't necessarily always spaced evenly by rows, the lags can sometimes change. However, for the purpose of my script, it's OK to just look at the average lag for the whole column.

  The way I was going to do this (and can do it manually, but would rather automate it with script) is to create a Lag column of :X and then take the difference of :X-:Lag(X), then take the Col Mean of that. Formula (math)-wise it should look like:

ave_lag = Col Mean(:X-:Lag(X))

  The trouble I'm having is creating the new column with the Lag formula in it. Every time I try to evaluate the script, it places the literal text of what I write in the formula and not the intended column name. I've tried the  dt<<New Formula Column(Operation("Lag")) command, but this produces a column named :"Lag[X]", and this name is not JSL "friendly" as the real column name X can change, and I need this to be a variable in the main script.

  I've also tried Eval(Substitute(Expr())), but it literally puts in the quoted string, not the column as a reference, such as :X.

For example, in the table I'm working with, I've tried this code:

Eval(
	Substitute(
			Expr(
				dt << New Column( "temp", Formula( Lag( __tcols__  ) ) )
			),
		Expr( __tcols__ ), xcols[1]
	)
);

 Which produces a "temp" column, but the formula is the following:

The name of the column is t, and the variable xcols={"t"}. The formula should look like Lag(:t), a reference to the column, not the column name.

I've even tried this other code to extract the column number of :t (it can change within the data table) and then insert the Column(#) into the formula, but this also doesn't work.

collist = dt << Get Column Names( string ); //creates a list of the column names
colnum = Loc( collist, xcols[1] )[1]; //locates the position in the list of the script variable xcols[1]. 

//This previous part basically gets the column number of the local variable xcol in the data table (it can change).

//This is the script where I'd like it to substitute 
Eval(
	Substitute(
			Expr(
				dt << New Column( "temp", Formula( Lag( Column( __tcols__ ) ) ) )
			),
		Expr( __tcols__ ), colnum
	)
);

Which also produces a "temp" column, but the formula is the following:

  I thought this should be easier than I'm finding out, but I must be doing something wrong/strange because I'm not getting the results I would expect.

  Any suggestions on how to calculate the average lag of a column is much appreciated. I only need a single number, ultimately, I don't need the extra columns.

Thanks!,

DS

[#2 · Accepted solution]

Try:

Eval( Eval Expr(
	dt << New Column( "TEMP", "Numeric", <<Set Each Value( Lag( Expr( As Name( xcols[1] ) ) ) ) )
) )

You need to tell JSL that the string is actually a name (for it to look at it like a variable), thus the As Name() function

[#3 · Accepted solution]

Hi @ErraticAttack ,

  Thanks for your input, and although it did the job of properly inserting the name of the column into the column formula, that particular piece of code inside my other existing code ended up causing problems with references to some display boxes that need to be updated based on user input. Unfortunately, it caused too many problems to go this route because of that, so I ended up changing the code and working with just the values from the column of interest: first by creating a matrix and then simply calculating the row (n+1)-row(n) difference of the column and averaging the matrix.

n = Matrix( N Rows( dt ), 1 );
For( i = 1, i <= N Items( n ) - 1, i++,
	n[i] = (dt:xcols[i + 1] - dt:xcols[i]) / timefactor
);
deltat = Round( Mean( n ), 3 );

Where "timefactor" is a variable that depends on whether or not the column is in units of days, min, hours, etc.

So, thank you for your solution to the original problem of how to send a variable to a new column function, but unfortunately, I had to go another way in order to solve the functionality of the code as a whole.

Thanks!,

DS