cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
KevinBrunner
Level II

Differentiate between entire column and current row

Hello,

 

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

KevinBrunner_2-1694158597655.png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Differentiate between entire column and current row

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

View solution in original post

15 REPLIES 15
txnelson
Super User

Re: Differentiate between entire column and current row

Here is one way to solve the problem

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

Re: Differentiate between entire column and current row

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.

KevinBrunner_0-1694170229319.png

 

txnelson
Super User

Re: Differentiate between entire column and current row

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

Re: Differentiate between entire column and current row

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.

hogi
Level XII

Re: Differentiate between entire column and current row

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.

jthi
Super User

Re: Differentiate between entire column and current row

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

 

-Jarmo
txnelson
Super User

Re: Differentiate between entire column and current row

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.

Jim
ron_horne
Super User (Alumni)

Re: Differentiate between entire column and current row

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

ron_horne_0-1694164659219.png

 

KevinBrunner
Level II

Re: Differentiate between entire column and current row

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!