Differentiate between entire column and current row

[#1]

Hello,

I am struggling to implement a simple "contains" formula. Here's a sample data set and formula:

Here's the text: Contains(:Column 1, :Column 2)

The issue: I think it is always checking the current row of Column 1 (whole). I want it to check the entire column, not just the current row. You can see the E and B are in Column 2, but the output is saying that it does not find the part in the whole. 

How do I explicitly say, check the entire column and not just the current row of the column?

Thank you very much!

[#2]

Here is one way to solve the problem

As Constant( colList = :Column 1 << get values );
Contains( colList, :Column 2 );

[#3]

I tried this. It's much closer, but not quite what I'm looking for. It's counting something more than what I'm looking for. It says the B is 5 and E is 2. I just need true or false, 1 or 0.

[#4 · Accepted solution]

As Constant( colList = :Column 1 << get values );
Contains( colList, :Column 2 ) > 0;

[#5]

Thanks again for your help.

I was able to simplify the code:

Contains(:Column 1 << Get Values, :Column 2) > 0;

It does exaclty what I want. Maybe this helps you in the future.

Cheers.

[#6]

for a small table this is fine - for a large table you will hate it.
It will run the 

:Column 1 << Get Value

for every single row that is calculated  - and get very slow.

[#7]

You can also use use As Constant() to evaluate only on first row

Names Default To Here(1);

row_count = 10000; // 100 000 starts getting slow
dt = New Table("test",
	Add Rows(row_count),
	New Column("Die X", Numeric, Nominal),
	New Column("Die Y", Numeric, Nominal),invisible
);

dt << Begin Data Update;
For Each Row(dt, 
	:Die X = Random Integer(1, N Rows(dt));
	:Die Y = Random Integer(1, N Rows(dt) * 2);
);
dt << End Data Update;
wait(0);

start = HP Time();
dt << New Column("Formula", Numeric, Nominal, Formula(
	Contains(:Die X << Get Values, :Die Y) > 0;
));
dt << Run Formulas;
Show(HP Time() - start);

start = HP Time();
dt << New Column("Formula As Constant", Numeric, Nominal, Formula(
	As Constant(x_m = :Die X << get values);
	Contains(x_m, :Die Y) > 0;
));
dt << Run Formulas;
Show(HP Time() - start);

[#8]

Your code performs the <<get values for each row.

As Constant( colList = :Column 1 << get values );
Contains( colList, :Column 2 ) > 0;

only runs the <<get values    once.  With a large data table, this code will be far more efficient.

[#9]

Hi @KevinBrunner ,

i would think the most obvious way of comparing columns (rather than rows) in JMP would be using table join.

perhaps something like this

// New data table
// → Data Table( "Untitled" )
dt1 = New Table( "Untitled" );
dt1 << New Column("Column 1", Character, "Nominal", Set Values({"A", "B", "C", "D", "E"}));

// New data table
// → Data Table( "Untitled 2" )
dt2 = New Table( "Untitled 2" );
dt2 << New Column("Column 2", Character, "Nominal", Set Values({"A", "E", "F", "B", "H"}));

// Join data tables
// → Data Table( "Untitled 3" )
dt1 <<  Join(
	With( dt2 ),
	Match Flag( 1 ),
	By Matching Columns( :Column 1 = :Column 2 ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
);

it can also be done using clicking rather than scripting. as in the picture below

[#10]

Maybe I can clarify the goal a little more.

In column 3 I want a function that returns a 1 if the current row in column 2 is found anywhere in column 1. So in my example, column 3 should display 1, 1, 0, 1, 0. Because the E and B in those rows are found in column 1. 

Thank you very much!