BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted

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

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

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

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

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

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

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

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

Re: Scanning a Description

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

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

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

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.