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.