topic Adding two columns in a table when one of columns can be blank in Discussions

Adding two columns in a table when one of columns can be blank

AT — Thu, 30 Aug 2018 10:05:54 GMT

Hi,

I have added bunch of data tables (Combined data table, attached) and found out the column Y and SPC_Y are indeed the same variable (Name change during the test). So I need to combine these columns. I wrote the following script and the added column (Y+SPC_Y) is blank.

dt=current data table();
 
// Two Columns Name that are the same Y and SPC_Y
 
dt<<new column("Y+SPC_Y", continious);
 
for each row(:Name("Y+SPC_Y")=:Y+:SPC_Y);
 
dt<<delete column("SPC_Y");
dt<< delete column("Y");
 
dt:Name("Y+SPC_Y")<< set name("SPC_Y");

I appreciate your help. Thanks

Re: Adding two columns in a table when one of columns can be blank

txnelson — Wed, 29 Aug 2018 01:11:04 GMT

I miss read the issue, please egnore the verbage below

If you delete a column that is being used in a formula that defines another column, the values in the column that is based upon the deleted column can not be calculated and therefore becomes a missing value. To fix this issue, remove the formula from the column before deleting the columns.

dt = Current Data Table();
 
// Two Columns Name that are the same Y and SPC_Y
 
dt << New Column( "Y+SPC_Y", continious );
 
For Each Row( :Name( "Y+SPC_Y" ) = :Y + :SPC_Y );

dt:Name( "Y+SPC_Y" ) << delete formula;
 
dt << delete column( "SPC_Y" );
dt << delete column( "Y" );
 
dt:Name( "Y+SPC_Y" ) << set name( "SPC_Y" );

Re: Adding two columns in a table when one of columns can be blank

AT — Wed, 29 Aug 2018 00:55:14 GMT

Thanks Jim for the suggestion. However, It is still showing the output column SPC_Y all blank.

Should I impute missing values as zero in avoid blank output?

Thanks for your continuous support.

Re: Adding two columns in a table when one of columns can be blank

txnelson — Wed, 29 Aug 2018 01:17:47 GMT

My bad.....I missread the issue......what the real issue is, is that for each row in your calculation, one of the 2 values has a missing value. In JMP a valid value that has a missing value mathematically applied to it results in a missing value. A missing value is not a zero. To get around that, the Sum() function does egnore missing values, so you can use it

dt = Current Data Table();
 
// Two Columns Name that are the same Y and SPC_Y
 
dt << New Column( "Y+SPC_Y", continious );
 
For each row( :Name( "Y+SPC_Y" ) = sum(:Y , :SPC_Y ));
 
dt << delete column( "SPC_Y" );
dt << delete column( "Y" );
 
dt:Name( "Y+SPC_Y" ) << set name( "SPC_Y" );

Re: Adding two columns in a table when one of columns can be blank

AT — Thu, 30 Aug 2018 00:59:58 GMT

Thanks Jim. It works now.