nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Differentiate between entire column and current row

KevinBrunner

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!

Accepted answers

txnelson

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

All comments

txnelson

Here is one way to solve the problem

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

ron_horne

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

KevinBrunner

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!

KevinBrunner

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.

txnelson

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

hogi

... 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.

KevinBrunner

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

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

edit:
I added a 4th version similar to the first one, but with the formula split into 2 steps.

Names Default To Here(1);

row_count = 10;
dt = New Table("test",
	Add Rows(row_count),
	New Column("col1", set each value(row())),
	New Column("col2", set each value(Random Integer(1,row_count*2))));

wait(0);

New Column( "compact Formula",	Formula( Contains( :col1 << Get Values, :col2 ) > 0 ),	Set Property("Color Gradient",{"Stoplight Bad to Good"(1), Range( {0, 1, 0.5} )}),Color Cell by Value);
New Column( "Formula with 2 steps",	Formula( myList = :col1 << Get Values ; Contains( myList, :col2 ) > 0 ),Set Property("Color Gradient",{"Stoplight Bad to Good"(1), Range( {0, 1, 0.5} )}),Color Cell by Value);
New Column( "Formula with local variable",	Formula( Local( {t0 = :col1 << get values()}, Contains( t0, :col2 ) > 0 ) ),Set Property("Color Gradient",{"Stoplight Bad to Good"(1), Range( {0, 1, 0.5} )}),Color Cell by Value);
New Column( "Formula As Constant",	Formula(As Constant( t_1 = :col1 << get values );		Contains( t_1, :col2 ) > 0),Set Property("Color Gradient",{"Stoplight Bad to Good"(1), Range( {0, 1, 0.5} )}),Color Cell by Value);

new window("info", << modal,Text Box ("Please confirm: after the first evaluation,
all values are correct.
And  after running the next 2 lines of code, the values are correctly updated.
But now, let's try to change values in col1 manually. will the computed columns be updated?"));
:col1 << set each value(-10);
:col1[1] = :col2[2];

edit:
added color cell by value to make the issue better visible
green: match is found

red: no match is found
the column Formula with local variable works. It can be used as a reference to judge the result of the other columns.

hogi

... 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

Good news: contains also support matrices as first argument

But it doesn't seem to be comfortable with an expression like

:col1 << Get Values