nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Adding Formula Columns based on Column Name List

BatteryTamer359

Hi there! I'm trying to create a script that will generate Formula columns based on if a Column Name contains the right keyword. I think I'm misusing the way lists translate strings into other functions. Currently using JMP 17

Ptable = Current Data Table ();

col list = Ptable << Get Column Names(String);

if(debug == 1, Print(col list));

For(i=1, i<ncols(Ptable), i++,
	If( Contains( col list[i], "Pressure"),
		New Column("Column", Numeric, "Continuous", Format("Best", 12), Set Selected);
		Column("Column") << Set Formula(
			((15 / (0.016 *  /*###*/Num( :Resistance[1] ) /*###*/)) * Num( Column(col list[i]))
			-3.75) * 6.89476);
		Column("Column") << Set Name(col list[i] + " (kPa)");
	)
);

Accepted answers

jthi

You have to evaluate column correctly there. Expr(NameExpr(AsColumn())) is one option

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

mycol1 = "height";
mycol2 = "weight";

Eval(EvalExpr(
	new_col = dt << New Column("Col", numeric, continuous, formula(
		Expr(NameExpr(AsColumn(dt, mycol1))) * Expr(NameExpr(AsColumn(dt, mycol2)))
	))	
));


show(new_col << get formula);

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute

All comments

jthi

You have to evaluate column correctly there. Expr(NameExpr(AsColumn())) is one option

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

mycol1 = "height";
mycol2 = "weight";

Eval(EvalExpr(
	new_col = dt << New Column("Col", numeric, continuous, formula(
		Expr(NameExpr(AsColumn(dt, mycol1))) * Expr(NameExpr(AsColumn(dt, mycol2)))
	))	
));


show(new_col << get formula);

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute

txnelson

Here is a modification of your code using the Big Class data table as an example.

Names Default To Here( 1 );

Open( "$SAMPLE_DATA/big class.jmp" );
debug = 1;

Ptable = Current Data Table();

col list = Ptable << Get Column Names( String );

If( debug == 1,
	Print( col list )
);

// Columns are going to be added to the data table.  The For() clause
// will change the end point of the looping if ncols() is in the 
// termination value
ncolumns = N Cols( Ptable );

For( i = 1, i <= ncolumns, i++,
	If( Contains( col list[i], "eight" ), 
	// The formula equation needs to be fully parsed before applying it to
		// the columns definition
		Eval(
			Substitute(
					Expr(
						New Column( col List[i] || " (kPa)",
							Numeric,
							"Continuous",
							Format( "Best", 12 ),
							Set Selected,
							Formula(
								(((15 / (0.016 * Num( :age[1] ))) * Num( _col_ )) - 3.75) * 6.89476
							)
						)
					),
				Expr( _col_ ), parse(col list[i] )
			)
		)
	)
);

BatteryTamer359

Nevermind! Issue was with my array clist not pulling the column names into a list!

@jthi Does naming a column also require an Expression Eval? The page you shared showed a Concat with Char( Array[#] ) working just fine, but I get an error trying to rename columns, code shown below. I'm not adding columns, so I'm not sure why there would be a mismatch between the N Cols and the counter in the For loop.

clist = Ptable << Get Column Names( String );
num_col = N Cols(Ptable);

For(i=3, i < num_col, i++,
	Column(i) << Set Name(Char(clist[i]) || " °C"); //Error on this line
);

"Subscript Range in access or evaluation of 'clist[ /*###*/i]' , clist[/*###*/i]"

jthi

Evaluation isn't required when setting the name. You have other issue in your code:

This part of your code doesn't really do anything

clist = {Ptable << Get Column Names( String )};

Remove {} around the << Get Column Names and then it should work

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

clist = dt << Get Column Names(String);
num_col = N Cols(dt);

For(i = 3, i < num_col, i++,
	Column(i) << Set Name(Char(clist[i]) || " °C"); //Error on this line
);