675569_en-US

liliconcom

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:

 

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!

Basic Data Analysis and ModelingRe: 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 );

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

 

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!

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.

 

Re: Differentiate between entire column and current row

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

Re: Differentiate between entire column and current row

... following @txnelson 's  second suggestion:

https://community.jmp.com/t5/Discussions/Differentiate-between-entire-column-and-current-row/m-p/675677/highlight/true#M86260 

Alternatively:

Local( {t0 = :Column 1 << get values}, Contains( t0, :Column 2 ) > 0 )

which generates a local variable:

which is calculated once and used for every row.

Worse:

t0 = :Column 1 << get values;
Contains( t0, :Column 2 ) > 0;

This version will get the values again and again for every single row.

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.

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),<BR />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);

 

Re: Differentiate between entire column and current row

and how about table updates?
If Jmp updates the entries in col 1 , all 3 variants get updated - so everything seems to be fine ...

Names Default To Here(1);

row_count = 10; // 100 000 starts getting slow
dt = New Table("test",
 Add Rows(row_count),
 New Column("col1", set each value(Random Integer(1, row_count))),
 New Column("col2", set each value(Random Integer(1,row_count*2))));

wait(0);

New Column( "Formula", Formula( Contains( :col1 << Get Values, :col2 ) > 0 ));
New Column( "Formula with local variable", Formula( Local( {t0 = :col1 << get values()}, Contains( t0, :col2 ) > 0 ) ));
New Column( "Formula As Constant", Formula(As Constant( t_1 = :col1 << get values );  Contains( t_1, :col2 ) > 0));

:col1 << set each value(-10);
:col1[1] = :col2[1];

 

Re: Differentiate between entire column and current row

... till you try it manually.

I was interested if the as constant notices the update. No issue here.

But somehow surprising, there is an issue with the "Formula" Column which I actually did not expect:

After a first, correct calculation, the first of the calculated columns stops working properly and starts to compare the column entries 1 by 1 !?!

(my code is correct, isn't it?)

According to Jmp manual, the first argument of contains has to be

How good that it allows a JSL symbol (like t0) as well

But it doesn't seem to be comfortable with a more complicated expression.

 

 

Re: Differentiate between entire column and current row

JMP 19.0.4 -> the same issues.