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 quickly filter out all the rows in a data table with multi-col...

- 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

May 28, 2020 2:07 AM
(1584 views)

If a data table has more than 100 columns.Given that columns 11 -- 100 are in numeric format, how can use JSL to quickly filter out all rows in column 11 -- 100 that are not null.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

This should give you what you want.

```
Names Default To Here( 1 );
dt = Current Data Table();
dt << select where( Number( dt[Row(), Index( 11, 100 )] ) == 90 );
d3 = dt << Subset( Output Table( "t" ), Selected Rows( 1 ), selected columns( 0 ) );
```

Jim

9 REPLIES 9

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

Created:
May 28, 2020 2:16 AM
| Last Modified: May 28, 2020 2:25 AM
(1579 views)
| Posted in reply to message from lwx228 05-28-2020

I see that this is a way to sum multiple columns in the same row.

`sumVec = (VSum(mat`))`;`

I want to do this, but I don't know how to figure out the number of non-null columns in the same row in this way.

Thanks!

```
dt = Current Data Table();
mat = dt[0, 11 :: 100];
nu = (Number( mat` ))`;
dt << New Column( "text", Numeric, Continuous, Values( nu ) );
dt << Select Where( :text == 90 );
d3 = dt << Subset( Output Table( "t" ), Selected Rows( 1 ), selected columns( 0 ) );
```

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

No Number was found.

Highlighted

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

This should give you what you want.

```
Names Default To Here( 1 );
dt = Current Data Table();
dt << select where( Number( dt[Row(), Index( 11, 100 )] ) == 90 );
d3 = dt << Subset( Output Table( "t" ), Selected Rows( 1 ), selected columns( 0 ) );
```

Jim

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

This is an efficient way to do it.

How can do add a new column to the table and count the number of columns in row 11-100 that are not null?

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

- I know you know how to add a column to a data table
- You were just shown how to find rows that are not null
- So I must not be understanding what you are asking

Jim

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

I just want to add how do know if need to do the calculation what's the better way to do it?

When the number of columns is small, I use this method:

`sum(if(abs(As Column(11))>=0,1),if(abs(As Column(12))>=0,1),if(abs(As Column(13))>=0,1),if(abs(As Column(14))>=0,1),if(abs(As Column(15))>=0,1))`

Thanks!

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

Just for clarification,

`Number( dt[Row(), Index( 11, 15 )] )`

will not give you the same results as

`sum(if(abs(As Column(11))>=0,1),if(abs(As Column(12))>=0,1),if(abs(As Column(13))>=0,1),if(abs(As Column(14))>=0,1),if(abs(As Column(15))>=0,1))`

The first equation will give you the count of non blank(null) values, while the second equation gives you the count of non zero values.

Additionally, a simpler version of the second equation is:

`Sum( As Column( 11 ) != 0, As Column( 12 ) != 0, As Column( 13 ) != 0, As Column( 14 ) != 0, As Column( 15 ) != 0 )`

Jim

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

Thank Jim!

I've tried. They're equivalent.

```
Number( dt[Row(), Index( 11, 15 )] ==5)
sum(if(abs(As Column(11))>=0,1),if(abs(As Column(12))>=0,1),if(abs(As Column(13))>=0,1),if(abs(As Column(14))>=0,1),if(abs(As Column(15))>=0,1))==5
```

Is for non-null values, not non-zero values.

Highlighted
##
Re: How to use JSL to quickly filter out all the rows in a data table with multi-column values that are not empty?

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

You are correct....my error

Jim

Article Labels

There are no labels assigned to this post.