70378_en-US

liliconcom

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

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=""></NEW>

 

  

I appreciate your help. Thanks

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

Thanks Jim. It works now.

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

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

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

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