4 Replies Latest reply: Oct 6, 2016 11:11 AM by Kim Stroupe RSS

    Two tables match char between range

    Kim Stroupe

      Hello,

      I have two tables and need to do an IntervalMatch but can't because my data is a character and not a number.  Can this be done in Qlik?

       

      Table A

      Min_Serial          Max_Serial         Task       Complied

      123A-0001           123A-0009           A             Y

      123A-0010           123A-9999           A             N

       

      Table B

      Serial                    

      123A-0003

       

       

      In SQL I can do if(Serial between Min_Serial and Max_Serial, Serial, 0)

       

       

      Thank you in Advance

      Kim

        • Re: Two tables match char between range
          Sunny Talwar

          How about may be like this:

           

          TableA:

          LOAD *,

            SubField(Min_Serial, '-', 1) as Serial,

            SubField(Min_Serial, '-', 2) as Min,

            SubField(Max_Serial, '-', 2) as Max;

          LOAD * INLINE [

              Min_Serial,          Max_Serial,        Task,      Complied

              123A-0001,          123A-0009,          A,            Y

              123A-0010,          123A-9999,          A,            N

          ];

           

          TableB:

          LOAD Serial as Serial_Main,

            SubField(Serial, '-', 1) as Serial,

            SubField(Serial, '-', 2) as Key;

          LOAD * INLINE [

              Serial             

              123A-0003

          ];

           

          Left Join (TableB)

          IntervalMatch(Key, Serial)

          LOAD Min,

            Max,

            Serial

          Resident TableA;

           

          Left Join (TableB)

          LOAD *

          Resident TableA;

           

          DROP Table TableA;


          Capture.PNG

            • Re: Two tables match char between range
              Stefan Wühl

              Another option:

               

              TableA:

              LOAD * INLINE [

                  Min_Serial,          Max_Serial,         Task,       Complied

                  123A-0001,           123A-0009,           A,             Y

                  123A-0010,           123A-9999,           A,             N

              ];

               

              TableB:

              JOIN

              LOAD * INLINE [

                  Serial             

                  123A-0003

              ];

               

              RESULT:

              NOCONCATENATE

              LOAD * Resident TableA

              WHERE Serial follows Min_Serial and Serial precedes Max_Serial;

               

              DROP Table TableA;