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