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
AT
AT
Level V

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("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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

 

Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

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

 

Jim
AT
AT
Level V

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.

 

txnelson
Super User

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

 

Jim
AT
AT
Level V

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

Thanks Jim. It works now.