cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lwx228
Level VIII

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

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
txnelson
Super User

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

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

View solution in original post

9 REPLIES 9
lwx228
Level VIII

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

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

 

 

lwx228
Level VIII

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

No Number was found.

2020-05-28_17-30.png

txnelson
Super User

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

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
lwx228
Level VIII

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

Thank Jim!
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?

txnelson
Super User

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

  1. I know you know how to add a column to a data table
  2. You were just shown how to find rows that are not null
  3. So I must not be understanding what you are asking
Jim
lwx228
Level VIII

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

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!

txnelson
Super User

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

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
lwx228
Level VIII

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

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.

2020-05-29_12-52.png

txnelson
Super User

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

You are correct....my error

Jim