51732_en-US

liliconcom

How do I use JSL to change the value in cells with missing values to zero?

When splitting a table, I end up with cells in multiple columns with missing values.  I want to change these values to 0.  The number of rows and split columns will vary, based upon data being pulled in through an SQL query.  How do I iterate through these selected columns and make this change from missing value to 0.

 

Thanks

Re: How do I use JSL to change the value in cells with missing values to zero?

Agreed that in this instance it's not necessary but it's a tremendously useful pattern.  Take an arbitrarily complex piece of JSL.  Particularly one where it's difficult to parameterise the constituent components.  Bracket your variables with carets (^var^) and paste the whole lot inside an "Eval-Parse-Eval Insert" pattern.  Problem solved.

Re: How do I use JSL to change the value in cells with missing values to zero?

Thanks, thomasz.  Works great!!

Re: How do I use JSL to change the value in cells with missing values to zero?

You can do it in three steps by hand:

Select the columns

Choose standardize attributes and then recode.

Recode all .'s to 0.

If you want to do it by jsl, you could do it like this:

dt=current data table();
colnames = dt << get column names( numeric,string );
For( i = 1, i <= N Items( colnames ), i++,
 column(dt,colnames[i])[dt<<GET rows="" where=""></GET>

Kind regards, Thomas

Re: How do I use JSL to change the value in cells with missing values to zero?

Here is the same idea as @cwillden but without having to use Eval(Parse())

Names Default To Here( 1 );
dt = Current Data Table();

numericColumnNames = dt << get column names( numeric, string );

For( i = 1, i <= N Items( numericColumnNames ), i++,
 Try(
  Column( dt, numericColumnNames[i] )[dt << get rows where(
   Is Missing( (As Column( dt, numericColumnNames[i] )) )
  )] = 0
 )
);

Re: How do I use JSL to change the value in cells with missing values to zero?

Try something like this:

dt = Current Data Table();
cols = dt << Get Selected Columns();

For( i = 1, i <= N Items( cols ), i++,
 Eval( Parse( Eval Insert( "^cols[i]^[dt << Get Rows Where(Is Missing(^cols[i]^))] = 0" ) ) )
);

I ended up using the Eval-Parse-Eval Insert because the Is Missing function was not liking something like Is Missing(cols[i]) or Is Missing(Column(cols[i])).  Using the Eval-Parse-Eval Insert always feels a bit like a hack, but it will get you what you need.