Return "True" or "False" if multiple columns contain string

[#1]

Hi,

I would like to return "False" if any column data contains "hello" else return a "True"

I am only able to process 1 column with if, any ideas?

[#2]

Many ways of doing this. Do you need formula, is setting values enough, JSL or not, interactive solution?

Here is one possible formula, but I would remove it after it has been calculated once, as it won't re-evaluate in similar manner as formulas usually do (this might not be a problem in this case thou).

If(N Items(Loc(Current Data Table()[Row(), 1::N Cols()], "hello")) > 0,
	"FALSE"
,
	"TRUE"
);

This script should give a little extra explanation, it won't create new column for you though. Open log and run this, you can also change Row() = 1 to Row() = 2 and so on, to see how it behaves on different rows

Names Default To Here(1);

dt = New Table("Untitled 9",
	Add Rows(3),
	New Column("Column 1", Character, "Nominal", Set Values({"hello", "bye", "bye"})),
	New Column("Column 2", Character, "Nominal", Set Values({"hello", "bye", "bye"})),
	New Column("Column 3", Character, "Nominal", Set Values({"hello", "bye", "bye"})),
	New Column("Column 4", Character, "Nominal", Set Values({"hello", "hello", "bye"})),
	New Column("Column 5", Character, "Nominal", Set Values({"hello", "bye", "bye"}), Set Display Width(75)),
	New Column("Column 6", Character, "Nominal", Set Values({"hello", "bye", "bye"}))
);

Row() = 1;
Show(Current Data Table()[Row(), 1::N Cols()]);
Show(Loc(Current Data Table()[Row(), 1::N Cols()], "hello"));
Show(N Items(Loc(Current Data Table()[Row(), 1::N Cols()], "hello")));
Show(N Items(Loc(Current Data Table()[Row(), 1::N Cols()], "hello")) > 0);

Edit:

Removed extra "," from the formula

[#3]

Hi, 

I have a set of data with few thousand rows but would need to post process the data with a jmp script. So far my script has filtered out all the noise. Just need to know how to implement the solution you provided

[#4]

In terms of excel, this is my formula:

=IF(COUNTIF(R21602:AC21602,"HELLO")>0,"FALSE","TRUE")

[#5]

 You would create new formula character column Create Formulas in JMP (jmp.com) as last column in the data table and copy paste the formula I provided. After that you could re-open the column properties by double clicking the column header and remove formula if you want to. If you have JMP16 enhanced log should give you script to perform this if it is needed.

I had one extra "," in the earlier post (I will edit it out):

If(N Items(Loc(Current Data Table()[Row(), 1::N Cols()], "hello")) > 0,
	"FALSE"
,
	"TRUE"
);

[#6]

Hi,

I tried out the formula, but its only blank. Is there a way to do it as like excel =IF(COUNTIF(R21602:AC21602,"HELLO")>0,"FALSE","TRUE"), where we can only target specific range of columns to check for "Hello"?

Formula: 

dt << New Column("Scan_RESULT", Character, Nominal Formula(If(N Items(Loc(dt[Row(), 1::N Cols()], "hello")) > 0)),
"FALSE"
,
"TRUE"
);

[#7 · Accepted solution]

Here is my approach, which adds in the ability to set the range.

If( Row() == 1,
	dt = Current Data Table();
	rangelow = 2;
	rangehigh = 14;
);
If( Contains( dt[Row(), Index( rangelow, rangehigh )], "hello" ),
	"False",
	"True"
);

I have attached a sample data table with the formula already embedded in the column.  I have also included @jthi response in a new column called Scan_RESULT