topic Re: Creating a column with yes/no value based on 6 other columns with yes/no values in Discussions

Creating a column with yes/no value based on 6 other columns with yes/no values

rj69 — Tue, 06 Jun 2017 17:24:40 GMT

Hi all, I'm using JMP 11.2.1. Without using script, is there a way I can, in a data table, create a new column with yes/no values based on the yes/no values of 6 other columns? Basically, I would like to have a record in the new column have a "yes" value if ANY of the values of that record in the 6 other columns are a "yes" and a "no" value if NONE of the values of that record in the 6 other columns are a "yes." Thank you.

Re: Creating a column with yes/no value based on 6 other columns with yes/no values

txnelson — Tue, 06 Jun 2017 19:25:59 GMT

All you have to do, is to create a new column, right click on the column header, and select "Formula". The Formula window will pop up. Double click on where it says "No Formula" and an editor window will pop up. Paste the following formula into the window. In the formula I used the letters A-F to specify the 6 columns you said you had to compare against. Just replace the A-F with the column names.

If(
	:A=="No", "No",
	:B=="No","No",
	:C=="No","No",
	:D=="No","No",
	:E=="No","No",
	:F=="No","No",
	"Yes"
)

What the formula is doing, is marching down the 6 columns. If any one of them has the value "No" it will set the columns value to "No". If none of them are equal to "No" then the column will have the value "Yes"

Re: Creating a column with yes/no value based on 6 other columns with yes/no values

rj69 — Wed, 07 Jun 2017 12:39:19 GMT

Thank you for the idea, but it didn't seem to work. Every single row resulted in a "Yes" value being returned in the new column.

Following the logic, I thought it might work in reverse - marching down the columns, it should return a "Yes" value if there is a "Yes" value for any one of the 6 "comparison" columns, and a "No" if there were no "No" values across the 6 columns. When I edited the formula, however, a "No" value was returned for every single row.

Re: Creating a column with yes/no value based on 6 other columns with yes/no values

txnelson — Wed, 07 Jun 2017 13:03:48 GMT

The formula I listed is not a question of doubt. It works. See image below and attached file

What may be of issue is that your data may have mixed case. "No" is not equal to "no", or "NO". If this is the case, then I suggest

If(
	Uppercase( :A ) == "NO", "No",
	Uppercase( :B ) == "NO", "No",
	Uppercase( :C ) == "NO", "No",
	Uppercase( :D ) == "NO", "No",
	Uppercase( :E ) == "NO", "No",
	Uppercase( :F ) == "NO", "No",
	Uppercase( "Yes"
)

Also, please note the ":" in front of each column name. This is important for JMP to understand that it needs to look into a column by the specified name

Re: Creating a column with yes/no value based on 6 other columns with yes/no values

rj69 — Wed, 07 Jun 2017 15:29:42 GMT

Jim,

Thanks for the follow-up. I figured it out. I needed to do a few things:

I had to adjust your formula, because I was looking for the "opposite" outcome. My intent with your example would have yielded "Yes" values for all records in Column 7 since at least one "Yes" was present in each of the 7 rows. The only way a "No" would have been returned if a record featured "No" values in all columns a-f.
But that wasn't enough, as I initially had all "No" values for my new column. I wondered if different data types in the reference columns were throwing off the matching. Yes, the data types were listed as Integer while I was trying to match to a column with Character data type. (My original source file was converted from SPSS and I overlooked the column type.)
I changed the data types to Character, and also noticed Value Lables were being used, so the "No" and "Yes" were actually zeroes and ones. I went back to the formula and changed replaced the "No" and "Yes" with 0 and 1, and that's when it worked.

Good to know about the case sensititviy and ":" protocol. Still relatively new to JMP and I don't use it as often as I'd like. Thanks again for your help.

Robert

Re: Creating a column with yes/no value based on 6 other columns with yes/no values

txnelson — Wed, 07 Jun 2017 15:43:28 GMT

Glad you figured out the issues and got the problem solved.