topic Re: Use column variable in IF expression in Discussions

Use column variable in IF expression

HubP_SDe — Fri, 09 Jun 2023 22:04:49 GMT

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:

replacing the hardcodedl column name with an Eval() method does not work
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!

Re: Use column variable in IF expression

jthi — Tue, 02 Feb 2021 14:17:05 GMT

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

Re: Use column variable in IF expression

Byron_JMP — Wed, 03 Feb 2021 12:55:00 GMT

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.

Re: Use column variable in IF expression

HubP_SDe — Tue, 02 Feb 2021 15:52:17 GMT

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

Re: Use column variable in IF expression

HubP_SDe — Tue, 02 Feb 2021 16:05:38 GMT

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

Re: Use column variable in IF expression

Byron_JMP — Tue, 02 Feb 2021 16:12:30 GMT

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

Re: Use column variable in IF expression

HubP_SDe — Tue, 02 Feb 2021 16:21:28 GMT

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

Re: Use column variable in IF expression

hogi — Mon, 30 Jan 2023 23:40:43 GMT

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;