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

Scanning a Description

llawless

Hi everybody!

I would like to write a formula to scan Column, Description, pick up various text strings, and return a "YES" if one of them is present and "NO" if not present. The text strings of interest are only part of the data in the Column, Description. I am interested in all forms of the same thing, "40 oz", "40 ounce", etc. I want to return YES if any of these are present.

I wrote the following formula,

If(
N Rows/*###*/(Contains(
{"40 oz" ; "40 ounce" ; "40oz" ; "40 Ounce" ; "40 Oz" ;
"40 OZ"},
Empty()
)) > 0,
"Yes",
"NO"
)

However, JMP returns NO for all rows even ones where I can see the descriptions I have captured above. Is there a way to use "LOCATE" that would be better for this?

Thanks!

Accepted answers

dale_lehman

I believe I was correct in my earlier comment. Your formula needs to have the complete "Contains()" function after each "OR" statement. I've attached what I suggested and it appears to do what you want.

Sample Data Subset_JMP forum.jmp

uday_guntupalli

@llawless
I used the same script on your data table and here are the results .

dt = Current Data Table(); // Keep your data table open. 


dt << New Column("Test",Character,Continuous,Formula(If( !Is Missing( Regex( Char( :Description ), "oz" ) ) | !Is Missing( Regex( Char( :Description ), "ounce" ) ),:Description,"No Match")));

All comments

uday_guntupalli

@llawless ,
Hello, Let me give this a shot. I am assuming the data is in rows :

dt = Current Data Table(); 

Col = Column(dt,1); 

Vals = Col << Get Values; 

DesList = List(); 

for(i = 1 , i <= N Items(Vals), i++, 
		If(!IsMissing(Regex(Char(Vals[i]),"oz")) | !IsMissing(Regex(Char(Vals[i]),"ounce")) | !IsMissing(Regex(Char(Vals[i]),"ounce")) | !IsMissing(Regex(Char(Vals[i]),"Oz")),
			Insert Into(DesList,Vals[i]); 
		  );
   );
   
Show(DesList);

If these were Column Names you could simply replace in the above code and use it instead.

ColNames = dt << Get Column Names("String");

What I don't follow about your question is that the data is in "Column Description" - do you mean Column Name?

Hope this helps - else can you kindly clarify your question or provide sample data .

llawless

Thanks!

What I meant was the Column name is Description. Within that column, I want to select rows that have the text strings of interest (e.g., 40 oz).

dale_lehman

I could be wrong, but don't you need an OR statement inside of the contains function? If I am right, then the absence of OR is just looking for that entire string of entries and not finding them all at once. But I might be wrong.

uday_guntupalli

dt = Current Data Table(); 

ColNames = dt << Get Column Names("string"); 

DesList = List(); 

for(i = 1 , i <= N Items(ColNames ), i++, 
		If(!IsMissing(Regex(Char(ColNames [i]),"oz")) | !IsMissing(Regex(Char(ColNames [i]),"ounce")) | !IsMissing(Regex(Char(ColNames [i]),"ounce")) | !IsMissing(Regex(Char(ColNames [i]),"Oz")),
			Insert Into(DesList,ColNames [i]); 
		  );
   );
   
Show(DesList);

You can extract the column names using the Get Column Names function and use the code I provided

llawless

Hmm, I'm not picking up the descriptions. Is the code you provided just for scanning column names. I want to scan rows under a known column.

Another way:

New Column( "40 oz",
	Numeric,
	"Nominal",
	Format( "Best", 12 ),
	Formula( N Items( Regex Match( :Description, "40.*[oO]([zZ]|unce)" ) ) > 1 ),
	Value Labels( {0 = "No", 1 = "Yes"} ),
	Use Value Labels( 1 ),
	Set Display Width( 57 )
);