Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Adding two columns in a table when one of columns can be blank

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Aug 28, 2018 4:21 PM
| Last Modified: Aug 30, 2018 3:05 AM
(4115 views)

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
Aug 28, 2018 5:13 PM
| Last Modified: Aug 28, 2018 6:11 PM
(4105 views)
| Posted in reply to message from AT 08-28-2018

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Thanks Jim. It works now.

Article Labels

There are no labels assigned to this post.