topic Creating a formula column from a list ( JSL ) in Discussions

Creating a formula column from a list ( JSL )

FN — Fri, 09 Jun 2023 16:06:11 GMT

I want to create a simple formula for adding columns that are coming from a list.

The list can vary in the number of elements.

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Bands Data.JMP" );

col_list = {"varnish pct", "ink pct", "solvent pct" };

// expected formula (hard coded)
dt  << New Column( "mysum",
	Formula( Sum( :varnish pct, :ink pct, :solvent pct ) )
	);

This solution works for a Col Sum.

https://community.jmp.com/t5/Discussions/Using-list-of-columns-in-formulas/td-p/405133

Re: Creating a formula column from a list ( JSL )

txnelson — Thu, 23 Mar 2023 02:54:52 GMT

Here is one way of handling the issue

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Bands Data.JMP" );

theList = {"varnish pct", "ink pct", "solvent pct"};

col_list = {"varnish pct", "ink pct", "solvent pct"};

Eval(
	Parse(
		"dt  << New Column( \!"mysum\!",
	Formula( " ||
		concat items( theList, "," ) || " ) )
	);"
	)
);

Because formulas are open code, the actual is not evaluated as it is generated, Therefore, it has to be a completed statement for JMP to process it properly.

Re: Creating a formula column from a list ( JSL )

FN — Thu, 23 Mar 2023 05:35:40 GMT

The code works, but it was missing "Sum()".

If you edit the answer, I will accept it. Thanks :)

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Bands Data.JMP" );

col_list = {"varnish pct", "ink pct", "solvent pct"};

Eval(
	Parse(
		"dt  << New Column( \!"mysum\!",
	Formula( Sum(" ||
		concat items( col_list, "," ) || " ) )
	);"
	)
);

Re: Creating a formula column from a list ( JSL )

jthi — Thu, 23 Mar 2023 06:40:22 GMT

I try to avoid using Eval(Parse()) when ever I can (even though it is fairly often the easiest method). Usually in cases like this (now) I build the expression and evaluate that expression inside the formula

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Bands Data.JMP" );

col_list = {"varnish pct", "ink pct", "solvent pct"};

// 1
sum_expr = Expr(Sum());
For Each({item}, col_list,
	Insert Into(sum_expr, Name Expr(As Column(dt, item)));
);
//show(Name Expr(sum_expr));
Eval(EvalExpr(
	dt  << New Column( "mysum",
		Formula(Expr(Name Expr(sum_expr)))
	)
));

// 2
col_listing = Transform Each({item}, col_list,
	Name Expr(As Column(dt, item));
);
Eval(Substitute(
	Expr(dt << New Column( "mysum", Formula(_sums_))),
	Expr(_sums_), Substitute(col_listing, Expr(List()), Expr(Sum()));
));