topic Create new columns by loop formula calculation with specific column names in Discussions

Create new columns by loop formula calculation with specific column names

MarkovRootFox46 — Mon, 11 Sep 2023 06:31:33 GMT

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!

Re: Create new columns by loop formula calculation with specific column names

jthi — Mon, 11 Sep 2023 06:56:19 GMT

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. is good post regarding how to build formulas from variables

Re: Create new columns by loop formula calculation with specific column names

MarkovRootFox46 — Mon, 11 Sep 2023 08:04:05 GMT

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_)

Re: Create new columns by loop formula calculation with specific column names

jthi — Mon, 11 Sep 2023 09:56:00 GMT

Just try it and check the results and formula column property