Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Scanning a Description

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 13, 2017 12:51 PM
(2624 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

@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

Uday

15 REPLIES 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Scanning a Description

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Scanning a Description

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.