4 Replies Latest reply: Jul 22, 2015 5:19 AM by Gareth Schoeman RSS

    most efficient way to return set from two tables where id match, and salesdate > effectivedate

      Can someone tell me the best way in Qlikview to get those 2 tables to return the results in Table 3.  In this scenario, the sales consultant changes the manager periodically and this is stored in Table 2.  The sales records contain the consultant id, date, etc.  The report should do analysis by consultant and the manager at the time the sale was recorded.

       

      Table 1 Sales:

      Date,  Consultantid

      10/01/2011, 10

      20/02/2011,10

      25/03/2011,20

      5/05/2011,20

       

      Table 2 ConsultantManagerHistory.

      Consultantid, Effectivedate, Managerid

      10, 01/01/2011, 100

      10, 01/02/2011, 200

      20, 01/01/2011, 300

      20, 01/05/2011,400

       

       

      Table 3: the sales records to return the consultant manager id based on the effective date from the consultant history

      Date,Consultantid, Managerid

      01/01/2011, 10, 100

      20/02/2011,10, 200

      25/03/2011,20, 300

      5/05/2011,20, 400

       

      Best Regards

      Simona

        • Re: most efficient way to return set from two tables where id match, and salesdate < effectivedate
          Sushil Kumar

          hi,

           

          try this

           

          Temp:
          LOAD Date(Date#(Date,'DD/MM/YYYY')) as Date,Consultantid Inline
          [
          Date,Consultantid
          10/01/2011, 10
          20/02/2011,10
          5/05/2011,20
          25/03/2011,20
          ]
          ;

          NoConcatenate
          A:
          LOAD
          Date,
          Consultantid,
          Consultantid&rowno() as Key
          Resident Temp
          Order by Consultantid,Date;

          DROP Table Temp;

          Temp:
          LOAD Date(Date#(Effectivedate,'DD/MM/YYYY')) as Effectivedate,Consultantid as cid,Managerid Inline
          [
          Consultantid, Effectivedate, Managerid
          10, 01/01/2011, 100
          10, 01/02/2011, 200
          20, 01/05/2011,400
          20, 01/01/2011, 300
          ]
          ;

          Left Join(A)
          B:
          LOAD
          cid&RowNo() as Key,
          Managerid
          Resident Temp
          Order by cid,Effectivedate;

          DROP Table Temp;

          • Re: most efficient way to return set from two tables where id match, and salesdate > effectivedate

            Hi Kumar,

             

            This is not going to work, it returned blank manager ids, when I added more sales

             

            ConsultantidDateKeyManagerid
            102/01/2011101100
            1010/01/2011102200
            1020/02/2011103
            2025/03/2011204400
            2029/03/2011205
            205/05/2011206

             

             

            • Re: most efficient way to return set from two tables where id match, and salesdate > effectivedate
              Aaron Morgan

              Hi Simona,

               

              IntervalMatch and Slowly Changing Dimensions is what you want to follow in order to achieve what you're after.

               

              You first need to create an interval from your single dates and from there you can use intervalmatch to match the correct date to each interval.

               

              As an example, I got yours to work using this script:

               

              Let vEndDate = Num('01/06/2011');

              Sales:
              Load * Inline [
              Date, Consultantid
              10/01/2011, 10
              20/02/2011, 10
              25/03/2011, 20
              05/05/2011, 20
              ]
              ;

              History:
              Load * Inline [
              Consultantid, Effectivedate, Managerid
              10, 01/01/2011, 100
              10, 01/02/2011, 200
              20, 01/01/2011, 300
              20, 01/05/2011, 400
              ]
              ;

              NoConcatenate
              HistoryReverse:
              Load *
              Resident History
              Order by Consultantid, Effectivedate desc;

              Drop Table History;

              Left Join(HistoryReverse)
              Intervals:
              Load
              Consultantid,
              Managerid,
              Date(Effectivedate) as StartDate,
              If(Consultantid=Peek(Consultantid), Date(Previous(Effectivedate)-1), Date($(vEndDate))) as EndDate
              Resident HistoryReverse;

              Drop Field Effectivedate;

              Inner Join
              IntervalMatch:
              IntervalMatch(Date, Consultantid)
              Load
              StartDate,
              EndDate,
              Consultantid
              Resident
              HistoryReverse;

               

               

              But that's just an example and you may want to do it slightly differently. Happy to explain any point of the above if need be.

               

              Aaron

              • Re: most efficient way to return set from two tables where id match, and salesdate < effectivedate
                Gareth Schoeman

                Hi Simona,

                 

                I would create an interval table from your manager history table and then do an interval match (see 3rd table for creating the intervals):

                 

                SALES:

                LOAD * INLINE [

                    Date, ConsultantID,SalesAmount

                    10/01/2011,10,100

                    20/02/2011,10,290

                    25/03/2011,20,200

                    05/05/2011,20,150

                ];

                 

                TMANAGER:

                LOAD * INLINE [

                  ConsultantID, EffectiveDate, ManagerID

                  10,01/01/2011,100

                  10,01/02/2011,200

                  20,01/01/2011,300

                  20,01/05/2011,400

                ];

                 

                CONSULTANTMANAGERHISTORY:

                load

                  if(Previous(ConsultantID) = ConsultantID ,previous(EffectiveDate)) as EndDate,

                  EffectiveDate as StartDate,

                  ConsultantID,

                  ManagerID

                Resident TMANAGER ORDER BY ConsultantID ASC, EffectiveDate DESC;

                 

                Drop Table TMANAGER;