cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lou
lou
Level III

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

1 ACCEPTED SOLUTION

Accepted Solutions
thomasz
Level IV

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

Kind regards, Thomas

View solution in original post

5 REPLIES 5
cwillden
Super User (Alumni)

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.

-- Cameron Willden
txnelson
Super User

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
	)
);
Jim
David_Burnham
Super User (Alumni)

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.

-Dave
thomasz
Level IV

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

Kind regards, Thomas

lou
lou
Level III

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

Thanks, thomasz.  Works great!!