topic Differentiate between entire column and current row in Discussions

Differentiate between entire column and current row

KevinBrunner — Fri, 08 Sep 2023 07:40:50 GMT

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!

Re: Differentiate between entire column and current row

txnelson — Fri, 08 Sep 2023 08:31:33 GMT

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

ron_horne — Fri, 08 Sep 2023 09:18:06 GMT

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

KevinBrunner — Fri, 08 Sep 2023 10:47:02 GMT

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

KevinBrunner — Fri, 08 Sep 2023 10:51:04 GMT

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

txnelson — Fri, 08 Sep 2023 11:02:10 GMT

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

Re: Differentiate between entire column and current row

hogi — Fri, 08 Sep 2023 18:42:50 GMT

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

KevinBrunner — Thu, 14 Sep 2023 09:09:52 GMT

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

hogi — Tue, 07 Nov 2023 16:30:35 GMT

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.

Re: Differentiate between entire column and current row

jthi — Thu, 14 Sep 2023 10:21:09 GMT

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

Re: Differentiate between entire column and current row

hogi — Sun, 05 Nov 2023 16:23:10 GMT

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.

Re: Differentiate between entire column and current row

hogi — Sat, 16 Sep 2023 16:35:16 GMT

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

(view in My Videos)

Re: Differentiate between entire column and current row

hogi — Sat, 11 May 2024 17:08:05 GMT

Hi @Jed_Campbell , @Byron_JMP , this is the community discussion we talked about on Wednesday.

After our discussion I added a version with "Formula as 2 steps" which works fine on my system (without any delay) - see the video. This was the version with the severe delay when you tried it on Wednesday.

With the

:col1 << Get Values

inside Contains, after each manual change in :col1, one needs a

current data table() << rerun formulas

to get the formula column updated.
Interesting that there is a difference between manual changes and changes via JSL ...

Re: Differentiate between entire column and current row

txnelson — Sat, 16 Sep 2023 18:51:39 GMT

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.

Re: Differentiate between entire column and current row

hogi — Sun, 05 Nov 2023 16:49:28 GMT

I wondered why sometimes a column gets updated and sometimes not. At first sight, I thought the difference is caused by using different versions of Jmp.

Now I found out that the version with

As Constant( t_1 = :col1 << get values );

just updates the values if the variable t_1 is defined before generating the formula column.

This video shows how the column behaves when t_1 is not defined before:

(view in My Videos)

Names Default To Here(1);

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

wait(0);

t_1 = {};

// disable the next line if you want the column "Formula as column"" to get updated after manual changes of column 1
delete symbols(t_1);

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

Re: Differentiate between entire column and current row

hogi — Thu, 12 Sep 2024 14:35:17 GMT

JMP18.1 -> same.

Re: Differentiate between entire column and current row

hogi — Mon, 09 Feb 2026 17:49:23 GMT

JMP 19.0.4 -> the same issues.