Create new columns by loop formula calculation with specific column names

[#1]

Hello dear jmp experts/users,

I have a fixed format data table with similar column name formats like this (always similar two column name distinguished by _1):

NAME, C1, P2 ... C1_1, P2_1 .... (actually many columns with this naming rule)

I'd like to create new columns with below formula: for exampe,

C1_cal by : C1-C1_1

P2_cal by : P2-P2_1

...

how do i use JSL to achieve loop for this? Thanks a lot!

[#2 · Accepted solution]

Quite a lot of assumptions made regarding column names and column ordering

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Name", Character, "Nominal", Set Values({})),
	New Column("Column1", Numeric, "Continuous", Format("Best", 12), Set Values([1])),
	New Column("Column2", Numeric, "Continuous", Format("Best", 12), Set Values([2])),
	New Column("Column1_1", Numeric, "Continuous", Format("Best", 12), Set Values([2])),
	New Column("Column2_1", Numeric, "Continuous", Format("Best", 12), Set Values([4]))
);

all_colnames = dt << Get Column Names("String");
Remove From(all_colnames, 1); // Drop first column

For Each({col_name}, all_colnames[1::(N Items(all_colnames)/2)],
	Eval(Substitute(
		Expr(
			dt << New Column(col_name|| "_cal", Numeric, Continuous, Formula(
				_firstcol_ - _secondcol_
			));
		),
		Expr(_firstcol_), Name Expr(AsColumn(col_name)),
		Expr(_secondcol_), Name Expr(AsColumn(col_name || "_1"))
	));
);

Scripting Index and Scripting Guide have good information regarding JMP Scripting. Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute is good post regarding how to build formulas from variables

[#3]

Thanks a lot!!!

if I'd like to change formular to  C1-3.5*C1_1:

should i change the code to below format?

_firstcol_ - 3.5*(_secondcol_)

[#4 · Accepted solution]

Just try it and check the results and formula column property