3 Replies Latest reply: Jun 20, 2012 9:03 AM by Nicolai Jørgensen RSS

    Complex Lookup; Help Please

      Hi,

       

      I am currently  building an application that involves very large data sets along with quite a complex lookup.  I am not quite sure how to go about this and would be grateful for any advice/help.

       

      Table A is the fixed table that I want to extract data from.

      Table B is the table I want to map data onto; this table will be very large.

       

       

      Table A

      covtypeusrange1range21234date1date2
      CGO0.118600061006200630001/01/201231/03/2012
      CGO0.118100011001200130001/04/201231/12/9999
      CVU18.140200021002200230001/01/201231/12/9999
      CVU40.150300031003200330001/01/201231/12/9999
      TGU0.11870071072073001/01/201231/12/9999
      TVO18.14080081082083001/01/201231/12/9999
      TVO40.15090091092093001/01/201231/12/9999
      OGU0.11810011012013001/01/201231/12/9999
      OGO18.14020021022023001/01/201231/12/9999
      OVU40.15030031032033001/01/201231/12/9999

       

       

       

      Table B

      KeyCovTypeUsSizeAreaDate
      1CGO16101/02/2012
      2CGO16101/05/2012
      3OGO20201/01/2012
      4CVU25301/07/2012
      5CVU42101/05/2012

       

       

      In essence I am trying to lookup based on:

      Cov = cov

      Type = type

      Us = us

      Size being between range1 and range2

      Area being equal to column 1, 2, 3, or 4

      Date being between date1 and date 2

       

      The data that needs to be returned is contained within columns 1, 2, 3 and 4 to give th following result:

       

       

      KeyCovTypeUsSizeAreaDateLookup
      1CGO16101/02/20126000
      2CGO16101/05/20121000
      3OGO20201/01/2012210
      4CVU25301/07/20122200
      5CVU42101/05/20123000

       

       

       

       

      Once again any help would be appreciated on this as i'm not really sure of the best way to start.

       

      Many thanks

        • Re: Complex Lookup; Help Please
          Stefan Wühl

          Ted,

           

          I think you can do this using intervalmatch. You need to to the matching more than once, since you are coping with date and size intervals.

           

          It could then work like this (I needed to recreate some area values because I failed loading your column names '1', '2',... correctly from the WEB):

           

          LOOKUP:

          LOAD cov,

               type,

               us,

               range1,

               range2,

               100+floor(RAND()*10) as A1,

               200+floor(RAND()*10) as A2,

               300+floor(RAND()*10) as A3,

               400+floor(RAND()*10) as A4,

               date1,

               date2

          FROM

          [http://community.qlik.com/thread/55783?tstart=0]

          (html, codepage is 1252, embedded labels, table is @1);

           

          TOMATCH:

          LOAD Key,

               Cov,

               Type,

               Us,

               Size,

               Area,

               Date

          FROM

          [http://community.qlik.com/thread/55783?tstart=0]

          (html, codepage is 1252, embedded labels, table is @2);

           

          inner join (TOMATCH) IntervalMatch (Size, Cov, Type, Us) LOAD range1, range2, cov as Cov, type as Type, us as Us Resident LOOKUP;

           

          inner join (TOMATCH) Intervalmatch (Date, Cov, Type, Us) LOAD date1, date2, cov as Cov, type as Type, us as Us Resident LOOKUP;

           

          left join (TOMATCH) LOAD cov as Cov, type as Type, us as Us, range1, range2, date1, date2, A1, A2, A3, A4 Resident LOOKUP;

           

          drop table LOOKUP;

           

          RESULT:

          NoConcatenate LOAD *, pick(Area, A1,A2,A3,A4) as Result Resident TOMATCH;

           

          drop table TOMATCH;

           

          //drop fields date1, date2, range1, range2, A1,A2,A3,A4;

           

          Hope this helps,

          Stefan

          • Re: Complex Lookup; Help Please
            Nicolai Jørgensen

            Hi

             

            This will also produce the result table, but Stefan's might be a litle nicer:

            Table_A:

            LOAD cov,

                 type,

                 us,

                 range1,

                 range2,

                       1 as Column,

                 [1] as Lookup,

                 date1,

                 date2

            FROM [230217.xlsx] (ooxml, embedded labels, table is A);

            LOAD cov,

                 type,

                 us,

                 range1,

                 range2,

                       2 as Column,

                 [2] as Lookup,

                 date1,

                 date2

            FROM [230217.xlsx] (ooxml, embedded labels, table is A);

            LOAD cov,

                 type,

                 us,

                 range1,

                 range2,

                       3 as Column,

                 [3] as Lookup,

                 date1,

                 date2

            FROM [230217.xlsx] (ooxml, embedded labels, table is A);

            LOAD cov,

                 type,

                 us,

                 range1,

                 range2,

                       4 as Column,

                 [4] as Lookup,

                 date1,

                 date2

            FROM [230217.xlsx] (ooxml, embedded labels, table is A);

             

             

             

             

            // Load Table B

            Table_B:

            LOAD Key,

                 Cov,

                 Type,

                 Us,

                 Size,

                 Area,

                 Date

            FROM [230217.xlsx] (ooxml, embedded labels, table is B);

             

             

            // Join Tabel A data on, and afterwards keep the rows that fulfill the criterias

             

             

            Left join (Table_B)

            Load cov as Cov,

                 type as Type,

                 us as Us,

                 Column as Area,

                 Lookup,

                 range1, range2, date1, date2

            resident Table_A;

             

             

            NoConcatenate

            Table_B_Final:

            Load *

            resident Table_B

            where

            Size >= range1 and

            Size <= range2 and

            Date >= date1 and

            Date <= date2;

             

             

            drop fields range1, range2, date1, date2 from Table_B_Final;

            drop tables Table_A, Table_B;

             

            How to attach?

             

            /Nicolai