topic Filtering data based on 2 fields in QlikView

Filtering data based on 2 fields

Thu, 06 Jun 2013 16:09:27 GMT

I have a data table that looks something like this:

RuleID FrValue ToValue

1 J8597 J9002

2 J9000 J9999

3 J9000 J9001

4 J9002 J9999

5 J9001 J9001

The customer wants to be able to input one value and return the rows in which the value is between the FrValue and ToValue fields. So, the input J9001 would bring back:

RuleID FrValue ToValue

1 J8597 J9002

2 J9000 J9999

3 J9000 J9001

5 J9001 J9001

Any ideas on how to accomplish this in an user friendly way?

Re: Filtering data based on 2 fields

Gysbert_Wassenaar — Thu, 06 Jun 2013 18:20:09 GMT

See attached example.

Re: Filtering data based on 2 fields

Fri, 07 Jun 2013 15:13:24 GMT

Thanks, Gysbert. The attachment "sort of" worked. (It worked for the example I gave, but my example was bad.)What I didn't mention in my original post is that I have more than just 'J' codes. They can begin with any letter and some of them are completely numeric without any letters. When I applied your example, it displayed too much data since it didn't exclude rows that begin with other letters or no letters. Here's my result set from your attached example:

RuleID      FrValue    ToValue
I58113      J9000      J9000
I58115      7 000       75556
I58094      J9000      J9000
I45986      J9000      J9999
I46118      J9000      J9999
I46288      J9000      J9999
I57831      J9000      J9000
I57829      J9000      J9000
I59293      C8921     C9254
I59294      G8694     G9142
I59295      J8597      J9002
I59299      01967      11047
I57130      6 311       67350
I54248      J9000      J9000
I54114      J9000      J9000
I59263      C8921     C9254
I59264      G8694     G9142