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.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating a new summary column based on 5 reference columns

rj69

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.

Accepted answers

Dan_Obermiller

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

Sample Data Table Revised.jmp

All comments

txnelson

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

rj69

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

Can you please attach a sample data table?

rj69

Certainly. Thank you.

Sample Data Table.jmp