cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Get the free JMP Student Edition for qualified students and instructors at degree granting institutions.
Choose Language Hide Translation Bar
View Original Published Thread

Formula to check if any of a list of items is contained in the rows of a column

datanaut
Level III

JMP for Mac 

Version 17.2.0 (701896)

 

I have a column "Data" with rows of number_number like this:

1_3

3_7

6_5

...

 

I want to test this column to see if some of the rows contain values from a list of values and then flag or label the rows which do.

 

I know I could construct an elaborate formula like this:

If( :Die X_Y == "3_9" | :Die X_Y == "3_4",
	1,
	0
)

But that's very laborious for long lists of different values to check.  I'd like to just be able to paste a list of values to check for in a formula, delimited by a space or comma.  

 

I found the function "Contains Item."  It works fine for one item but the help entry doesn't offer any insight on how to construct the "list" option.  I tried this (specifying the optional delimiter as a space):

If( Contains Item( :Die X_Y, "3_4 3_9", " " ),
	"exclude",
	"not exclude"
)

and this:

If( Contains Item( :Data, "3_4,3_9", "," ),
	"exclude",
	"not exclude"
)

I understand the default delimiter is a comma but wanted to enforce to find anything that works.  Neither Contains Item function is returning the sought-for behavior (logical 1 if there is a match, 0 if not).  If there is a better function to use please advise.  

 

This is a generalized problem I'd like to be able to solve -- inspecting a column to find matches with long lists of items.  

 

Thanks

 

11 REPLIES 11
hogi
Level XII


Re: Formula to check if any of a list of items is contained in the rows of a column

the reading is: 3 arguments

  1. x - a string of characters
  2. 3 options:
    - item to search
    - a list of several items - does one of hem match
    - a pattern to search
  3. optional argument: the delimiter to split x in words

 


@datanaut wrote:

So (to my simplistic reading) either x or the {list} could contain the words to be searched or the list of words to compare it with.  


-> yes.

 

 included a Column as in Jarmo's example 

For a column formula, this is always possible, along the idea:
For every row, JMP will take automatically the entry of the column in the respective row.
When you use Formula editor, you do this trick all the time - just without noting.

hogi
Level XII


Re: Formula to check if any of a list of items is contained in the rows of a column

A nice side effect:

This discussion made me think: das Contains also increase the speed of Col .. aggregations?

And believe it or not - it does. wow, it does!!!!

https://community.jmp.com/t5/Discussions/How-do-I-use-the-Col-Maximum-Formula-with-a-quot-where-quot... 

 

Any clue why the character comparison is SOOO slow?