cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ab0716
Level I

Select rows based on first character

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
ab0716
Level I

Re: Select rows based on first character

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

View solution in original post

3 REPLIES 3
vince_faller
Super User (Alumni)

Re: Select rows based on first character

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

Vince Faller - Predictum
ab0716
Level I

Re: Select rows based on first character

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

gzmorgan0
Super User (Alumni)

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

 

image.png