topic Re: How Do You Exclude Certain Rows Based On Characters in QlikView

How Do You Exclude Certain Rows Based On Characters

pnn44794 — Sun, 13 Oct 2019 00:40:58 GMT

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.

Re: How Do You Exclude Certain Rows Based On Characters

pnn44794 — Fri, 27 Oct 2017 00:36:37 GMT

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

sunny_talwar — Fri, 27 Oct 2017 00:48:25 GMT

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

Re: How Do You Exclude Certain Rows Based On Characters

pnn44794 — Fri, 27 Oct 2017 01:25:59 GMT

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)

Re: How Do You Exclude Certain Rows Based On Characters

pnn44794 — Fri, 27 Oct 2017 01:56:31 GMT

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

sunny_talwar — Fri, 27 Oct 2017 08:41:20 GMT

May be this

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

Re: How Do You Exclude Certain Rows Based On Characters

pnn44794 — Fri, 27 Oct 2017 14:23:08 GMT

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.