48582_en-US

liliconcom

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!

Re: Scanning a Description

Thank you!

 

 

Re: Scanning a Description

Thank you.  I did not enter the or command correctly previously.

Re: Scanning a Description

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

Re: Scanning a Description

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

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.

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.

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

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

Re: Scanning a Description

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

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 ? 
 

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.

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 

Re: Scanning a Description

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.

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

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 .