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]

... following @txnelson 's  second suggestion:

https://community.jmp.com/t5/Discussions/Differentiate-between-entire-column-and-current-row/m-p/675... 

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.

[#3]

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.

[#4]

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

FormulaBug.mp4

Video Player is loading.

Play Video

Play

Mute

Current Time 0:00

/

Duration 0:23

Loaded: 0%

0:00

Stream Type LIVE

Seek to live, currently behind liveLIVE

Remaining Time -0:23

1x

Playback Rate

Chapters

• Chapters

Descriptions

• descriptions off, selected

Captions

• captions settings, opens captions settings dialog
• captions off, selected

Audio Track

• en (Main), selected

Picture-in-PictureFullscreen

This is a modal window.

Beginning of dialog window. Escape will cancel and close the window.

Text

ColorWhiteBlackRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-Transparent

Background

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-TransparentTransparent

Window

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyTransparentSemi-TransparentOpaque

Font Size

50%75%100%125%150%175%200%300%400%

Text Edge Style

NoneRaisedDepressedUniformDropshadow

Font Family

Proportional Sans-SerifMonospace Sans-SerifProportional SerifMonospace SerifCasualScriptSmall Caps

Reset restore all settings to the default valuesDone

Close Modal Dialog

End of dialog window.

Close Modal Dialog

This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.

[#5]

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

[#6]

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:

contains_bug.mp4

Video Player is loading.

Play Video

Play

Mute

Current Time 0:00

/

Duration 0:08

Loaded: 0%

0:00

Stream Type LIVE

Seek to live, currently behind liveLIVE

Remaining Time -0:08

1x

Playback Rate

Chapters

• Chapters

Descriptions

• descriptions off, selected

Captions

• captions settings, opens captions settings dialog
• captions off, selected

Audio Track

• en (Main), selected

Picture-in-PictureFullscreen

This is a modal window.

Beginning of dialog window. Escape will cancel and close the window.

Text

ColorWhiteBlackRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-Transparent

Background

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-TransparentTransparent

Window

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyTransparentSemi-TransparentOpaque

Font Size

50%75%100%125%150%175%200%300%400%

Text Edge Style

NoneRaisedDepressedUniformDropshadow

Font Family

Proportional Sans-SerifMonospace Sans-SerifProportional SerifMonospace SerifCasualScriptSmall Caps

Reset restore all settings to the default valuesDone

Close Modal Dialog

End of dialog window.

Close Modal Dialog

This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.

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

[#7]

JMP18.1 -> same.