nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

lou

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

Accepted answers

thomasz

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

All comments

cwillden

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.

txnelson

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