topic Re: creating new columns with a for loop with column names and formula in Discussions

creating new columns with a for loop with column names and formula

FrequencyBison9 — Mon, 31 Jul 2023 20:34:09 GMT

I am trying to create a split table from an existing table and add new columns into the split tables each with a formula based on the columns that were split. However the code creates the new columns but gives me "Formula evaluation errors"

dt2 = Current Data Table();

// Create the split data table
dt2 = dt << Split(
	Split By( :S ),
	Split(
				:Name( "Median(data)" )
			),
	Group( :L, :m, :C, :P, :z, :x ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);
colname = {"B","C"};
colby = {"A","B"};

// Add new columns to the split data table
For( i = 1, i <= N Items( colname ), i++,
dt2 << New Column( colname[i]||"/"||colby[i],
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( column(colname[i]) / column(colby[i]) )
););

Re: creating new columns with a for loop with column names and formula

txnelson — Mon, 31 Jul 2023 20:56:46 GMT

JMP formula definition will not evaluate when being defined, only when run. Therefore, the formula needs to be presented to JMP in a fully defined form.

Column( colname[i] ) /  Column( colby[i] )

needs to presented to JMP as

Column( "B" ) / Column( "A" )

Using the Substitute function allows for the expanding of the code as required

// Add new columns to the split data table
For( i = 1, i <= N Items( colname ), i++,
	Eval(
		Substitute(
				Expr(
					dt2 << New Column( colname[i] || "/" || colby[i],
						Numeric,
						"Continuous",
						Format( "Best", 12 ),
						Formula( Column( __colname__ ) / Column( __colby__ ) )
					)
				),
			Expr( __colname__ ), colname[i],
			Expr( __colby__ ), colby[i]
		)
	)
);

Re: creating new columns with a for loop with column names and formula

FrequencyBison9 — Mon, 31 Jul 2023 22:26:55 GMT

I am still getting the same error, as below

Column B/A Formula Interrupted
Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Divide, Column( "B" ) /  /*###*/Column( "A" ) /*###*/
Formula evaluation errors have been ignored

Column C/B Formula Interrupted
Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Divide, Column( "C" ) /  /*###*/Column( "B" ) /*###*/
Formula evaluation errors have been ignored

Re: creating new columns with a for loop with column names and formula

txnelson — Tue, 01 Aug 2023 00:52:23 GMT

Try changing the Column() function to As Column() function

Re: creating new columns with a for loop with column names and formula

FrequencyBison9 — Tue, 01 Aug 2023 15:48:28 GMT

That worked, thanks!