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

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

lou — Wed, 21 Feb 2018 15:31:31 GMT

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?

cwillden — Wed, 21 Feb 2018 16:03:20 GMT

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.

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

txnelson — Wed, 21 Feb 2018 16:09:41 GMT

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?

thomasz — Wed, 21 Feb 2018 16:15:32 GMT

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(is missing(as column(dt,colnames[i])))]=0;
);

Kind regards, Thomas

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

lou — Wed, 21 Feb 2018 17:59:05 GMT

Thanks, thomasz. Works great!!

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

David_Burnham — Wed, 21 Feb 2018 18:16:00 GMT

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.