Subscribe Bookmark RSS Feed

Creating a new summary column based on 5 reference columns

rj69

Community Trekker

Joined:

May 9, 2016

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.Capture.JPG

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Dan_Obermiller

Joined:

Apr 3, 2013

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
txnelson

Super User

Joined:

Jun 22, 2012

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
rj69

Community Trekker

Joined:

May 9, 2016

Thanks for the response. I fixed the reference columns and output column by making them character type, but nothing returns when I apply the formula (with my real column names).  Then I thought for "None of these" that maybe JMP wanted the actual value "998" as opposed to the value label "None of these", but neither made the formua work. I don't receive any errors when I apply the formula, it's just that nothing populates the output column.

txnelson

Super User

Joined:

Jun 22, 2012

Can you please attach a sample data table?

Jim
rj69

Community Trekker

Joined:

May 9, 2016

Certainly. Thank you.

Dan_Obermiller

Joined:

Apr 3, 2013

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
rj69

Community Trekker

Joined:

May 9, 2016

Thanks. I understand the difference between data types and value labels, I just wasn't positive if the formula could refer to both data values and data types. So, the formula worked - eventually. It worked in the sample file immediately, but I couldn't figure out why it wouldn't initially work in my base file. It appears that JMP didn't like how my columns were named (originally named in the native SPSS file by our vendor), but once I eliminated special characters the formula worked.

txnelson

Super User

Joined:

Jun 22, 2012

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
rj69

Community Trekker

Joined:

May 9, 2016

Good to know. Thank you.