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

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

lwx228 — Thu, 28 May 2020 09:07:27 GMT

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!

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

lwx228 — Thu, 28 May 2020 09:25:28 GMT

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

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

lwx228 — Thu, 28 May 2020 09:32:57 GMT

No Number was found.

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

txnelson — Thu, 28 May 2020 10:37:21 GMT

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

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

lwx228 — Thu, 28 May 2020 11:53:17 GMT

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?

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

txnelson — Thu, 28 May 2020 14:18:04 GMT

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

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

lwx228 — Thu, 28 May 2020 23:26:53 GMT

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!

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

txnelson — Fri, 29 May 2020 00:26:49 GMT

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 )

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

lwx228 — Fri, 29 May 2020 10:51:18 GMT

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.

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

txnelson — Fri, 29 May 2020 11:06:24 GMT

You are correct....my error