How Do You Exclude Certain Rows Based On Characters

[pnn44794]

Hello:

I have a need to exclude certain rows of data (sample data attached) based on whether the data begins and ends with a number.  The field in the sample data that I need to do the filtering on is DM #.  The DM # needs to begin with a number, end with a number and be a length of 5.

So, a DM # of 28549 would be valid and should be included.  A DM # beginning with HLIM would not be valid.  Additionally, a DM # of 20180103 would not be valid. null's are not valid.  A DM # of 99999 would not be valid.  However, DM # 88888888888-xx where xx can be any number, would be valid and is the only exclusion to the examples I've given as invalid DM #'s.  Make sense?

As always, any and all help  / responses are appreciated.

[pnn44794]

P.S.  My thought is that this would be a calculated dimension.  I'm open to other suggestions.

[sunny_talwar]

You can use a calculated dimension or use set analysis... but your valid and invalids are quite varied.... can you may be pick one of the two and give all those that are either valids or invalids

[pnn44794]

Hey Sonny:

Yeah, you have a good point.  If only we could get people to be consistent..........  Anyway........

Ok.  I think a calculated dimension is what I need.  Here's my refined DM # requirements:

• Begins with a number and ends with a number
• 5 Numeric Characters in length
• Plus a DM # beginning with 88888 ( i.e. 88888888888-11 would be included)
  

[pnn44794]

Hello Sunny:

Just in case you reply (or anyone else for that matter), I'll be offline for awhile. Probably until tomorrow morning.

[sunny_talwar · Accepted solution]

May be this

If((IsNum([DM #]) and Len([DM #]) = 5 and [DM #] <> '99999') or ([DM #] Like '88888*'), [DM #])

[pnn44794]

Thank you Sunny.  This works.  I did though, forget that I could have a valid 4 digit DM #, so the final expression is this:

=If((IsNum([DM #]) and Len([DM #]) = 4 and [DM #] <> '99999') or (IsNum([DM #]) and Len([DM #]) = 5 and [DM #] <> '99999')  or ([DM #] Like '88888*'), [DM #])

Thank you again!  Very much appreciated.  I'll mark your answer as the correct one.