cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
Djtjhin
Level IV

New Column Formula with JSL

I'm trying to add new column formula to a table based on user input. The input can either be a single value or a column selection. 

Been trying couple methods but I couldn't get one which could handle this interchangeably. 

Sample script that I tried below.

 

Ideally I can keep the "newcolexpr" expression the same regardless of the input. This is mainly because I'm trying to deploy custom formula column which takes lots of inputs (i.e. >8 inputs)

 

Appreciate the help.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

//when selection is a single value
//desired outcome: a new column "Test" with formula (1*5)
selection = {1};

newcolexpr = expr(
	dt << new column("Test", formula(expr(selection[1])*5))
);
eval(eval expr(newcolexpr));

//when selection is a column (e.g. "height")
//desired outcome: a new column "Test" with formula ("height"*5)
selection = {"height"};

newcolexpr = expr(
	dt << new column("Test", formula(expr(selection[1])*5))
);
eval(eval expr(newcolexpr));
2 ACCEPTED SOLUTIONS

Accepted Solutions
jthi
Super User

Re: New Column Formula with JSL

Approaches on how to approach this depends a lot on the variables, inputs and formulas you have.

Other example:

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

formula_expr = Expr(Expr(dt << New Column("Test", Numeric, Formula(placeholder*5))));
selection = {1};
Eval(Substitute(formula_expr, Expr(placeholder), selection[1]));
selection = {:height};
Eval(Substitute(formula_expr, Expr(placeholder), selection[1]));

formula_expr2 = Expr(Expr(dt << New Column("Test", Numeric, Formula(Sum(placeholder)))));
selection = {1,3};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));
selection = {:height, :weight};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));

formula_expr3 = Expr(Expr(dt << New Column("Test", Numeric, Formula(Col Sum(placeholder)))));
selection = {:height, :age, :sex};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));


Some links:

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute 

Using list of columns in formulas 

Can you construct this without writing expression as a string? 

 

-Jarmo

View solution in original post

Djtjhin
Level IV

Re: New Column Formula with JSL

Sorry for the late response. Thanks @jthi !

I found that the following works for what I want to do:

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

new_col_expr = expr(
	dt << new column("Testing",formula(t1+t2+3))
);

Selection1 = {1,5};
if(Selection1[1] == 1,
	Substitute into(new_col_expr,
		expr(t1),
		Selection1[2]
	),
	Selection1[1] == 0,
	Substitute into(new_col_expr,
		expr(t1),
		Nameexpr(ascolumn(Selection1[2]))
	)
);

Selection2 = {0,"weight"};
if(Selection2[1] == 1,
	Substitute into(new_col_expr,
		expr(t2),
		Selection1[2]
	),
	Selection2[1] == 0,
	Substitute into(new_col_expr,
		expr(t2),
		Nameexpr(ascolumn(Selection2[2]))
	)
);

eval(evalexpr(new_col_expr))

Thanks for all the reference above though. They helped.

 

Happy New Year!

View solution in original post

4 REPLIES 4
jthi
Super User

Re: New Column Formula with JSL

Most likely you will have to check for the input and then create column based on that (you can do check inside the Expr in formula, but it is more clear this way). Below is one option:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

new_col_expr = Expr(
	cur_sel = selection[1];
	show(cur_sel);
	If(IsNumber(cur_sel),
		Eval(EvalExpr(dt << New Column("Test", Numeric, Formula(Expr(cur_sel)*5))));
	,
		Eval(EvalExpr(dt << New Column("Test", Numeric, Formula(Expr(NameExpr(AsColumn(cur_sel)))*5))));
	);
);

selection = {1};
new_col_expr;
selection = {"height"};
new_col_expr;
-Jarmo
Djtjhin
Level IV

Re: New Column Formula with JSL

Hey @jthi Thanks for the response. I was thinking like this as well, but what if I have more than 1 variable to consider in the formula ? Will nested-if be the only solution there (I'm really trying to avoid that) ?

I wonder if the syntax can handle a variable inside the expr() so that I can assign a number or NameExpr(asColumn()) based on the input type before substituting into the expression. 

jthi
Super User

Re: New Column Formula with JSL

Approaches on how to approach this depends a lot on the variables, inputs and formulas you have.

Other example:

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

formula_expr = Expr(Expr(dt << New Column("Test", Numeric, Formula(placeholder*5))));
selection = {1};
Eval(Substitute(formula_expr, Expr(placeholder), selection[1]));
selection = {:height};
Eval(Substitute(formula_expr, Expr(placeholder), selection[1]));

formula_expr2 = Expr(Expr(dt << New Column("Test", Numeric, Formula(Sum(placeholder)))));
selection = {1,3};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));
selection = {:height, :weight};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));

formula_expr3 = Expr(Expr(dt << New Column("Test", Numeric, Formula(Col Sum(placeholder)))));
selection = {:height, :age, :sex};
Eval(Substitute(formula_expr2, Expr(placeholder), selection));


Some links:

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute 

Using list of columns in formulas 

Can you construct this without writing expression as a string? 

 

-Jarmo
Djtjhin
Level IV

Re: New Column Formula with JSL

Sorry for the late response. Thanks @jthi !

I found that the following works for what I want to do:

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

new_col_expr = expr(
	dt << new column("Testing",formula(t1+t2+3))
);

Selection1 = {1,5};
if(Selection1[1] == 1,
	Substitute into(new_col_expr,
		expr(t1),
		Selection1[2]
	),
	Selection1[1] == 0,
	Substitute into(new_col_expr,
		expr(t1),
		Nameexpr(ascolumn(Selection1[2]))
	)
);

Selection2 = {0,"weight"};
if(Selection2[1] == 1,
	Substitute into(new_col_expr,
		expr(t2),
		Selection1[2]
	),
	Selection2[1] == 0,
	Substitute into(new_col_expr,
		expr(t2),
		Nameexpr(ascolumn(Selection2[2]))
	)
);

eval(evalexpr(new_col_expr))

Thanks for all the reference above though. They helped.

 

Happy New Year!