Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

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
Highlighted
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
Highlighted
lwx228
Level VII

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

 

 

Highlighted
lwx228
Level VII

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

Highlighted
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

Highlighted
lwx228
Level VII

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?

Highlighted
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
Highlighted
lwx228
Level VII

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!

Highlighted
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
Highlighted
lwx228
Level VII

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

Highlighted
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
Article Labels

    There are no labels assigned to this post.