topic Re: How to use JSL to filter out the number of rows according to the number of rows in a column? in Discussions

How to use JSL to filter out the number of rows according to the number of rows in a column?

lwx228 — Fri, 09 Jun 2023 23:27:44 GMT

For example, use A to filter out the number of rows whose age is equal to 7.

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

Re: How to use JSL to filter out the number of rows according to the number of rows in a column?

txnelson — Tue, 02 Jun 2020 04:19:40 GMT

Here is one way to do it

names default to here(1);
dt=open("$SAMPLE_DATA/big class.jmp");

dt << select where( col number(:height, :age) == 7);

dt7 = dt << subset( selected rows(1), selected columns(0));

Re: How to use JSL to filter out the number of rows according to the number of rows in a column?

lwx228 — Tue, 02 Jun 2020 10:25:12 GMT

Thank Jim!

dt << select where( Col Number( :height, :age ) == 7 );
dt << select where( Col Number( :weight, :age ) == 7 );

The effect is the same but this modification does not work:

dt << select where( Col Number( :age ) == 7 );

Don't understand the principle.

Re: How to use JSL to filter out the number of rows according to the number of rows in a column?

txnelson — Tue, 02 Jun 2020 05:01:11 GMT

The function Col Number() calculates the count of the column specified

col number(:height )

Counts the number of rows for the column :Height that are non missing values, so in the example with data table Big Class, it will return the value of 40.

The Col Number() function can have more than one value passed to it. The first value is the column for count the number of non missing values, and the 2nd through n additional columns, are "By Group" columns.

col number(:Height, :age )

The above function call, calculates the count of the non missing values of :Height for the rows current value of :age. So for row 1 in the data table, the function will only count the non missing values of :Height for the rows with the :Age value of 12. It will return the value 8. When it comes to row 9, the value of age is 13, and the number of non missing values of :Height for the rows with the Age value of 13 is 7.

But why are we counting the number of non missing values of :Height. For no other reason than we no it has no missing values, and therefore, it will return a proper count. We could actually use any of the numeric columns, since there are no non missing values for any of the numeric columns.

Initially, I was going to specify the function call like this

col number(:age, :age )

but changed it to :Height, since I thought using :Age twice would be confusing