Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

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.

Tags (1)
1 Solution

Accepted Solutions

Re: How Do You Exclude Certain Rows Based On Characters

May be this

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

6 Replies
Partner
Partner

Re: How Do You Exclude Certain Rows Based On Characters

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

Re: How Do You Exclude Certain Rows Based On Characters

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

Partner
Partner

Re: How Do You Exclude Certain Rows Based On Characters

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

Re: How Do You Exclude Certain Rows Based On Characters

Hello Sunny:

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

Re: How Do You Exclude Certain Rows Based On Characters

May be this

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

Partner
Partner

Re: How Do You Exclude Certain Rows Based On Characters

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.