BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
llawless

Community Trekker

Joined:

Aug 12, 2015

Scanning a Description

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
dale_lehman

Community Trekker

Joined:

Jan 29, 2015

Solution

Re: Scanning a Description

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.

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

Re: Scanning a Description

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

 

image.png

 

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"))); 
Best
Uday
15 REPLIES 15
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Scanning a Description

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

Best
Uday
llawless

Community Trekker

Joined:

Aug 12, 2015

Re: Scanning a Description

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

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Scanning a Description

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 

Best
Uday
llawless

Community Trekker

Joined:

Aug 12, 2015

Re: Scanning a Description

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.

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Scanning a Description

@llawless

 

dt = Current Data Table(); 

Col = Column(dt,1); // Replace 1 with the column number of your data table with the data of interest 

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

This was to address that case where you are scanning rows.  Did you try this ? 
 

Best
Uday
llawless

Community Trekker

Joined:

Aug 12, 2015

Re: Scanning a Description

Yes, I tried it, and the column returned no data.

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Scanning a Description

@llawless
  I can help you more if you post a sample data set . Since once is not available , let me demonstrate what I am doing through a sample data set in JMP. Kindly share the sample data if this doesn't answer your question . 

 

Clear Log(); Clear Globals(); 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

DesNames = List(); 

NamesList = dt:name << get Values; 


for(i = 1 , i <= N Items(NamesList), i++,
		If(!IsMissing(Regex(Char(NamesList[i]),"AL"))|!IsMissing(Regex(Char(NamesList[i]),"MA")),
			Insert Into(DesNames,NamesList[i]);
		  );
   );
   
Show(DesNames);

image.png

Best
Uday
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Scanning a Description

@llawless
  I read and re-read your question multiple times, perhaps you are after this . 

  This is essentially the same logic as before - however, I used it in a column formula. Let me know if this helps. 

Clear Log(); Clear Globals(); 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

DesNames = List(); 

dt << New Column("Test",Character,Continuous,Formula(If( !Is Missing( Regex( Char( :name ), "AL" ) ) | !Is Missing( Regex( Char( :name ), "MA" ) ),:name,"No Match"))); 
Best
Uday
llawless

Community Trekker

Joined:

Aug 12, 2015

Re: Scanning a Description

I really appreciate your help!

 

I have blinded some data in the attached file.  My real data set has many more rows and longer descriptions, but I hope this is enough to give you an idea.