creating formula using existing column names

[#1]

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!

[#2 · Accepted solution]

You have 2 issues going on here.

1. 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
2. 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")
		)
	);
);

[#3]

Thanks Jim it works!