Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

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

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

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

2020-06-02_11-04.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

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

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));
Jim

View solution in original post

Highlighted
txnelson
Super User

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

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

Jim

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

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

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));
Jim

View solution in original post

Highlighted
lwx228
Level VII

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

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.

Highlighted
txnelson
Super User

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

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

Jim

View solution in original post

Article Labels