topic Re: creating formula using existing column names in Discussions

creating formula using existing column names

midori555 — Fri, 09 Jun 2023 16:59:15 GMT

Names Default To Here( 1 );
dt = Current Data Table();
numcol=dt << Get Column Names(Numeric);
charcol=dt << Get Column Names( Character);


For( k = 1, k <= N Items( numcol ), k++,
colname=column(numcol[k]) <<Get Name;

dt << New Column( "site"||numcol[k],
formula(If(
	:ID == "item5", (:Column( dt, colname)* 0.5) / 20,
	:ID == "item8", (:Column( dt, colname) * 0.8) / 20,
	:Column( dt, colname)
)
)
);
);

Hi JMP users,

I have a data table that I need to do some conversions on numeric columns based on predefined criteria.

I'm getting error "Expression does not fit available column types at row 1 in access or evaluation of 'Bad Argument' ".

Any idea on how to resolve this? Appreciated!

Re: creating formula using existing column names

txnelson — Sat, 14 May 2022 03:08:46 GMT

You have 2 issues going on here.

When a formula is presented to a new column, it is not processed before being added as the formula. Therefore, when you are specifying
```
:Column(dt, colname)
```
JMP does not change it to the first numeric column name, then the second, etc. The JSL author must use code that will expand the reference into the complete/ finished JSL statement before passing it to the Format element
The column names in your data table, i.e. 1, 2, 3, 4 etc. will cause problems in JMP interpreting them, unless you specifically indicate they are to be interpreted as column names. Therefore, you need to use the :"column name"n structure

Below is a reworking of your code that produces the new columns you want

Names Default To Here( 1 );
dt = Current Data Table();
numcol = dt << Get Column Names( Numeric );
charcol = dt << Get Column Names( Character );


For( k = 1, k <= N Items( numcol ), k++,
	colname = Column( numcol[k] ) << Get Name;

	Eval(
		Substitute(
				Expr(
					dt << New Column( "site" || numcol[k],
						formula(
							If(
								:ID == "item5", (__colname__ * 0.5) / 20,
								:ID == "item8", (__colname__ * 0.8) / 20,
								__colname__ 
							)
						)
					)
				),
			Expr( __colname__ ), Parse( ":\!"" || colname || "\!"n")
		)
	);
);

Re: creating formula using existing column names

midori555 — Mon, 16 May 2022 18:21:52 GMT

Thanks Jim it works!