912570_en-US

liliconcom

Formula in jsl script

I have single column table of numeric type, I want to add column and give a formula where it will check each cell of the first column with a variable name passed in script, 

mycol << New Column ("belowCount", Numeric, Formula(If( col_to_check < limit_val, 1, 0 ) );

 

mycol is single column data table and col_to_check is reference first column and limit_val is variable

When I open table formula is not able to take the first column 

I also tried using : Column(1), Column(1) and also used Expr function which are of no use

Automation and ScriptingData AccessRe: Formula in jsl script

Correct syntax:

Eval(
	Eval Expr(
		mycol << New Column( "belowCount",
			Numeric,
			Formula( If( Expr( Name Expr( As Column( col_to_check ) ) ) < Expr( limit_val ), 1, 0 ) )
		)
	)
)

Re: Formula in jsl script

first thing, your example formula didn't have enough closing parentheses, so it may give an error for that. Second thing is that your "col_to_check" may have needed a ":" in front of it, unless it was a variable name of the column or it was exactly the same spelling and case.

 

Below is what I was able to use that I think did what you are looking for. I used Eval and Eval Expr at the start of the new column line to evaluate the variable to create the column.

names default to here(1);

mycol = current data table();
limit_val = 5;
eval(eval expr(mycol << New Column ("belowCount", Numeric, Formula(If( :col_to_check < expr(limit_val), 1, 0 ) ))));

Re: Formula in jsl script

You will have to evaluate the variables to formulas . This is still a good referenceInsert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute (thought it could use some updates). Here is one option using Eval(Substitute()) (for simple case like this, I would use Eval(EvalExpr()) which hogi did demonstrate)

Names Default To Here(1);

new_col = Eval(Substitute(
	Expr(mycol << New Column("belowCount", Numeric, 
		Formula(If(col_to_check < _limit_val_, 1, 0))
	)),
	Expr(col_to_check), Name Expr(AsColumn(mycol, 1)),
	Expr(_limit_val_), limit_val
));

And full example

Names Default To Here(1);

limit_val = 5;

mycol = New Table("Untitled",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Set Values([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]))
);

new_col = Eval(Substitute(
	Expr(mycol << New Column("belowCount", Numeric, 
		Formula(If(col_to_check < _limit_val_, 1, 0))
	)),
	Expr(col_to_check), Name Expr(AsColumn(mycol, 1)),
	Expr(_limit_val_), limit_val
));