Choose Language Hide Translation Bar
Highlighted
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" );``

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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
Highlighted
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
3 REPLIES 3
Highlighted
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
Highlighted
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
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
Article Labels