cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
datanaut
Level III

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

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?