topic Re: How can I make a conditional subset table without directly calling a column name? in Discussions

How can I make a conditional subset table without directly calling a column name?

Kelly — Mon, 28 Oct 2019 18:42:45 GMT

The first 3 lines of this script create a new data table where heights are less than 60. This part runs fine.
For the function that I would like to develop I would like to reference a column in a different way than using :height.

I have tried 4 different ways to reference the column, but none of them work. I have used the column function and get column reference function (please see below).

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
//This one works
dtSubgroup = dt << Subset(Rows(dt << get rows where(:height < 60)));
//None of these work
dtSubgroup = dt << Subset(Rows(dt << get rows where(Column("height") < 60)));
dtSubgroup = dt << Subset(Rows(dt << get rows where(Column(dt, "height") < 60)));
dtSubgroup = dt << Subset(Rows(dt << get rows where(Column(4) < 60)));
refList = dt << Get Column Reference( {"height"} );
dtSubgroup = dt << Subset(Rows(dt << get rows where(refList[1] < 60)));

Is there any reason why they do not work?
Any suggestion on a solution?

Thank you for your help.

Re: How can I make a conditional subset table without directly calling a column name?

Mark_Bailey — Mon, 28 Oct 2019 19:19:10 GMT

Try appending empty square brackets after the closing parenthesis of the Column() function. Like this:

Column("height")[] < 60

Does this change help?

Re: How can I make a conditional subset table without directly calling a column name?

Kelly — Mon, 28 Oct 2019 20:00:26 GMT

Thanks, this did exactly what I wanted.
May I ask why this works?
I have used the column function many times without using square brackets.
What is different about this case? What is the rule to follow?

Re: How can I make a conditional subset table without directly calling a column name?

Mark_Bailey — Mon, 28 Oct 2019 20:42:01 GMT

It does seem odd, doesn't it?

It is all about context. For example, you might use this in a column formula:

If( Column("height") < 60, 0, 1 ); // implicit assignment of Row() = current row number

Or you might use it in the For Each Row() function to accomplish the same thing in a script:

For Each Row(
	// implicit assignment of Row() = current row number
	If( Column("height" ) < 60,
		:status = "small",
		:status = "large"
	)
)

The context of the column formula and the iteration above is such that JMP is implicitly setting the Row() function for you. So JMP uses that information to figure out where to get or store the value (i.e., which row). That is not the context of the general case here:

dtSubgroup = dt << Subset(Rows(dt << get rows where(Column("height") < 60)));

The data table does not implicitly use the current row number when you tries to evaluate the comparison. You force it to by adding the empty subscript operator, [ ].