turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Creating a new summary column based on 5 reference...

Topic Options

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

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

A month ago
(380 views)

Hi all, I'm using JMP 11.2.1.Without using script, what would be the best approach to create a new summary column based on the following conditions? I have a screenshot below of what I would like to have.

- If ALL values of the reference columns Col1 - Col 5 are null, I would like a null value returned.
- If ANY of the 5 reference columns have a value that is NOT "None of these", I would like a single summary value returned, i.e. "An Item"
- If column 1 has a value of "None of these" (and it will only be column 1 with that value), I would like "None of these" returned as the summary value.

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

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

A month ago
(708 views)

Solution

You are being confused by data types and value labels. Col 1 has 998 as a code (text string), with a value label of "None of these". The real data is "998" as a character string. Similarly for Col2 through Col5. The values are "1", "2", etc. all as character strings. Your formula must be dealing with actual data values and actual data type, not the labels. Your formula should look like this:

If(

:Col1 == "998", "None of these",

Is Missing( Sum( Num( :Col2 ), Num( :Col3 ), Num( :Col4 ), Num( :Col5 ) ) ) == 0

| :Col1 != "", "An Item"

)

Dan Obermiller

8 REPLIES

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

A month ago
(377 views)

To start with, there is an issue with your sample data table. Your col1 appears to contain numeric and character items. JMP will only tollerate this if the column is a character column. So based on that assumption here is a formula that will work.

```
If( :Col1 == "None of these", "None of these",
IsMissing( sum(:Col2, :Col3, :Col4, :Col5) ) == 0 | :Col1 != "", "An Item"
);
```

Jim

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

A month ago
(372 views)

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

A month ago
(368 views)

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

A month ago
(363 views)

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

A month ago
(709 views)

You are being confused by data types and value labels. Col 1 has 998 as a code (text string), with a value label of "None of these". The real data is "998" as a character string. Similarly for Col2 through Col5. The values are "1", "2", etc. all as character strings. Your formula must be dealing with actual data values and actual data type, not the labels. Your formula should look like this:

If(

:Col1 == "998", "None of these",

Is Missing( Sum( Num( :Col2 ), Num( :Col3 ), Num( :Col4 ), Num( :Col5 ) ) ) == 0

| :Col1 != "", "An Item"

)

Dan Obermiller

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

A month ago
(350 views)

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

A month ago
(345 views)

If you have special characters in a column name, you can still referenct them by using the "Name()" function.

If you column name is:

A+4/#x

you can refer to it in your formula as:

:Name("A+4/#x")

Jim

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

A month ago
(343 views)