2 Replies Latest reply: Jun 7, 2013 11:13 AM by kslinker RSS

    Filtering data based on 2 fields

      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

          See attached example.

            • Re: Filtering data based on 2 fields

              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