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