Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

How Do You Exclude Certain Rows Based On Characters

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.

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

6 Replies
pnn44794
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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

May be this

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

pnn44794
Partner - Specialist
Partner - Specialist
Author

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.