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

[#1]

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

[#2]

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

[#3]

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.

[#4 · Accepted solution]

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

[#5]

Thanks Jim. It works now.