Use column variable in IF expression

[#1]

Dear Cummunity,

Below, I'm using several variables brought by a Dialog:

• colSelect: the column to be analyzed
• quantVal1 & quantVal2: Quantile values

In the following piece of script, I'm creating a "Categories" column, that will then be tabulated to obtain amounts for each quantile.

Two problems:

1. replacing the hardcodedl column name with an Eval() method does not work
2. how could I test if the column already exists, and if yes, update its formula?
   This would allow to refresh my whole analysis process with new column and quantiles, with out having to restart from scratch loading and processing the data table.

//Custom Quantiles amounts
dt << New Column( "Categories",
	Numeric, "Continuous",
	Format( "Best", 9 ),
	Formula(
		If( Eval( colSelect )/*:Total Time From CheckIn Collector To Exit*/ <= quantVal1,
			1,
			If( Eval( colSelect )/*:Total Time From CheckIn Collector To Exit*/ <= quantVal2,
				2,
				3
			)
		)
	),
	Set Property( "Value Order", {} ),
	Set Property(
		"Color Gradient",
		{{"Green to Orange to Red", 4099, {{0, 227, 0}, {255, 255, 0}, {252, 11, 11}}, {0, 0.5, 1}}, Range( {1, 3, 1} )}
	),
	Color Cell by Value,
	Set Display Width( 74 )
);

Thanks!

[#2]

1. Good thread how to replace values in formula:

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

If you don't need formula I would suggest using << Set Each Value.

2. You can get column name list with dt << Get Column Names("String") and use Contains() to check for columns in that list

This should work:

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
colSelect = 1;
quantVal1 = 1;
quantVal2 = 2;

//Custom Quantiles amounts
Eval(Eval Expr(dt << New Column( "Categories",
	Numeric, "Continuous",
	Format( "Best", 9 ),
	Formula(
		If( Expr( colSelect )/*:Total Time From CheckIn Collector To Exit*/ <= Expr(quantVal1),
			1,
			If( Expr( colSelect )/*:Total Time From CheckIn Collector To Exit*/ <= Expr(quantVal2),
				2,
				3
			)
		)
	),
	Set Property( "Value Order", {} ),
	Set Property(
		"Color Gradient",
		{{"Green to Orange to Red", 4099, {{0, 227, 0}, {255, 255, 0}, {252, 11, 11}}, {0, 0.5, 1}}, Range( {1, 3, 1} )}
	),
	Color Cell by Value,
	Set Display Width( 74 )
)));
colList = dt << Get Column Names("String");
Show(Contains(colList, "Categories")); //will return index

[#3]

Thanks a lot Jarmo,

I have partial solutions in your reply.

Still, the colSelect trick only works with a given numeric value, not a column name (forgot to mention)

Thanks to your example, here is more what it would look like (Expr (colSelect) not working in this updated context):

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
colSelect = "age";
quantVal1 = 13;
quantVal2 = 16;

//Custom Quantiles amounts
Eval(Eval Expr(dt << New Column( "Categories",
	Numeric, "Continuous",
	Format( "Best", 9 ),
	Formula(
		If( /*Expr( colSelect )*/:age <= quantVal1,
			1,
			If( /*Expr( colSelect )*/:age <= quantVal2,
				2,
				3
			)
		)
	),
	Set Property( "Value Order", {} ),
	Set Property(
		"Color Gradient",
		{{"Green to Orange to Red", 4099, {{0, 227, 0}, {255, 255, 0}, {252, 11, 11}}, {0, 0.5, 1}}, Range( {1, 3, 1} )}
	),
	Color Cell by Value,
	Set Display Width( 74 )
)));
colList = dt << Get Column Names("String");
Show(Contains(colList, "Categories")); //will return index

[#4]

since ColSelect is coming from a dialog, it may in fact be a list of one item.

If you evaluate ColSelect, what is returned? if it is {:colname}, then it is a list of one item

Try evaluating ColSelect[1], if it is from a list then :colname will be returned.

In this case eval() returns the list.

take a look at Eval list() too

there is a pdf that is SUPER helpful for understanding expressions attached to this post.

Expression Handling Functions: Part I - Unraveling the Expr(), NameExpr(), Eval(), ... Conundrum 

[#5]

Hi Byron,

The Show(colSelect) returns a simple string, not a list (I took care of this when passing the Dialog arguments):
colSelect = "Total Time From Check-In To Exit";

I will explore this PDF, Thanks

[#6]

maybe also look at these two arguments also:

     Column()

     As name()

you script might be trying to parse ColSelect as a text string rather than a column name

[#7 · Accepted solution]

Thanks again Byron,

After trying both, and reading some of the links provided, it was finally a mix of both whihc worked: AsColumn()

Thanks all!

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
colSelect = "age";
quantVal1 = 13;
quantVal2 = 16;

//Custom Quantiles amounts
Eval(Eval Expr(dt << New Column( "Categories",
	Numeric, "Continuous",
	Format( "Best", 9 ),
	Formula(
		If( AsColumn( colSelect )/*:age*/ <= quantVal1,
			1,
			If( AsColumn( colSelect )/*:age*/ <= quantVal2,
				2,
				3
			)
		)
	),
	Set Property( "Value Order", {} ),
	Set Property(
		"Color Gradient",
		{{"Green to Orange to Red", 4099, {{0, 227, 0}, {255, 255, 0}, {252, 11, 11}}, {0, 0.5, 1}}, Range( {1, 3, 1} )}
	),
	Color Cell by Value,
	Set Display Width( 74 )
)));
colList = dt << Get Column Names("String");
Show(Contains(colList, "Categories")); //will return index

[#8 · Accepted solution]

A slight disadvantage of this code: The variables in the Formula will not be defined anymore at a later time.
This can be seen by the red wiggles in the Formula Editor:

The result: When a value in the table changes in the future, the column will not be updated automatically.

A more robust code can be generated with an approach that I learned from @jthi :
Put NameExpr(As Column()) inside Expr().
Then the Formula will look like this:

 and column values will update in the future if input values get updated

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
colSelect ="age";
quantVal1 = 13;
quantVal2 = 16;

//Custom Quantiles amounts
Eval(Eval Expr(dt << New Column( "Categories",
	Numeric, "Continuous",
	Format( "Best", 9 ),
	Formula(
		If( Expr( NameExpr(As Column(dt, colSelect)) )/*:Total Time From CheckIn Collector To Exit*/ <= Expr(quantVal1),
			1,
			If( Expr( Name Expr(As Column(dt, colSelect)) )/*:Total Time From CheckIn Collector To Exit*/ <= Expr(quantVal2),
				2,
				3
			)
		)
	),
	Set Property( "Value Order", {} ),
	Set Property(
		"Color Gradient",
		{{"Green to Orange to Red", 4099, {{0, 227, 0}, {255, 255, 0}, {252, 11, 11}}, {0, 0.5, 1}}, Range( {1, 3, 1} )}
	),
	Color Cell by Value,
	Set Display Width( 74 )
)));

dt:age[12]=40;