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

- JMP User Community
- :
- Discussions
- :
- Select rows based on first character

- 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

Sep 21, 2018 3:31 PM
(6265 views)

Hi I have a column with a lot of numbers and some text. Below is a snipet of what my data looks like. I'm tring to hide and exclude all rows that aren't 7 digits long and don't start with 5 (i.e. I only want numbers like 5******).

5238403

5236577

3238403

ASKU

523660682

asku

2536614

NI

5236610

So far I know how to exclude all the rows that aren't 7 characters long, but I can't figure out how to select the rows that don't begin with 5 (e.g. 2536614).

dt<<select where(length(:column) != 7 );

dt<< hide and exclude (1);

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

For some reason that solution still left a few numbers that didn't fit my criteria, but when I split it up as two separate lines it works great! Thanks!

dt <<select where(left(:column, 1) != "5" );

dt <<select where(length(:column) != 7, current selection("extend") );

3 REPLIES 3

Highlighted
##

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

Re: Select rows based on first character

dt<<select where(length(:column) != 7 & left(:Column, 1) != "5");

Vince Faller - Predictum

Highlighted

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

For some reason that solution still left a few numbers that didn't fit my criteria, but when I split it up as two separate lines it works great! Thanks!

dt <<select where(left(:column, 1) != "5" );

dt <<select where(length(:column) != 7, current selection("extend") );

Highlighted
##

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

Re: Select rows based on first character

If the lot number must be 7 numeric digits, what begins with 5, one more condition needsto be added.

```
dt = current data table();
dt<<select where(length(:Column 1) != 7 | left(:Column 1, 1) != "5" | IsMissing(num(:Column 1)));
```

Also, new column using a formula can find matches using Regex. Column 2 of the table displayed below, uses the formula

Regex( :Column 1, "(5\d{6}\b)", "\0" ). the selected rows are those selected by the Select Where() command above.

Note row 10, 5Z3661A, would not be selected without the additional criteria IsMissing(num(:Column 1))

Article Labels

There are no labels assigned to this post.