Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How to use JSL to filter out the number of rows according to the number of rows ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jun 1, 2020 9:01 PM
| Last Modified: Jun 2, 2020 3:18 AM
(699 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
Jun 1, 2020 9:28 PM
| Last Modified: Jun 2, 2020 3:25 AM
(686 views)
| Posted in reply to message from txnelson 06-02-2020

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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