4 Replies Latest reply: May 21, 2018 7:47 PM by arjun rao RSS

    Incremental Load

    Steve Jones

      Here is my incremental load script , i will loading data on startdate field .

       

      Every time i copy a new file and reload this script my data seems to duplicated.Can some one please check what i am doing wrong below?

       

      //Creating LiveopsTelephonyData qvd

       

       

      //LiveopsTelephonyData:

      //LOAD [Campaign Name],

      //     [Start Date],

      //     [All Segments],

      //     [Answered Segments],

      //     [Average Time to Answer (minutes)],

      //     [Call Length (minutes)],

      //     [Average Call Length (minutes)],

      //     [Queue Length (minutes)],

      //     [Max Queue Length (minutes)],

      //     Abandon,

      //     [Abandon %],

      //     [Caller Talk Time (minutes)]

      //FROM

      //$(vQVDPath_QlikMart_QVD)LiveOps.csv

      //(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

      //

      //store LiveopsTelephonyData into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

      //

      //exit script;

       

       

      //Loading data from qvd

      LiveopsTelephonyData:

      LOAD [Campaign Name],

          date([Start Date],'MM/DD/YYYY') as [Start Date],

           [All Segments],

           [Answered Segments],

           [Average Time to Answer (minutes)],

           [Call Length (minutes)],

           [Average Call Length (minutes)],

           [Queue Length (minutes)],

           [Max Queue Length (minutes)],

           Abandon,

           [Abandon %],

           [Caller Talk Time (minutes)]

      FROM

      $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD

      (qvd);

       

       

      RecentUpdate:

      Load  Max(date([Start Date],'MM/DD/YYYY')) as MaximumDate

      FROM

      $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

       

       

      Let vLastUpdatedDate = Peek('MaximumDate',0,'RecentUpdate');

       

       

      drop Table LiveopsTelephonyData;

       

       

      Incremental:

      LOAD [Campaign Name],

            date([Start Date],'MM/DD/YYYY') as [Start Date],

           [All Segments],

           [Answered Segments],

           [Average Time to Answer (minutes)],

           [Call Length (minutes)],

           [Average Call Length (minutes)],

           [Queue Length (minutes)],

           [Max Queue Length (minutes)],

           Abandon,

           [Abandon %],

           [Caller Talk Time (minutes)]

      FROM

      $(vQVDPath_QlikMart_QVD)LiveOps.csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

      where ([Start Date]) > $(vLastUpdatedDate);

       

       

      Concatenate

      Load

      [Campaign Name],

           date([Start Date],'MM/DD/YYYY') as [Start Date],

           [All Segments],

           [Answered Segments],

           [Average Time to Answer (minutes)],

           [Call Length (minutes)],

           [Average Call Length (minutes)],

           [Queue Length (minutes)],

           [Max Queue Length (minutes)],

           Abandon,

           [Abandon %],

           [Caller Talk Time (minutes)]

      FROM

      $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

       

       

      store Incremental into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

       

       

      drop Table Incremental;

        • Re: Incremental Load
          surendra j

          Just go with this!!

          it have detailed explanation!!

          https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

          seems you are not using Where not exists logic to override the updated records..instead of creating a new record each time.

           

          -Surendra

          • Re: Incremental Load
            RODELL BASALO

            Here is some basic flow for doing incremnetal load....

             

            check if qvd exists

            a) if false = do full extract

            b) if true = do incremental

             

            1) FULL DATA: load the previous loaded data (full data)

            2) FLAG/ID : check the flag/id/date of the last loaded data (peek)

            3) OLD PARTIAL DATA: load partial data from full data(1) where data less than the value of peek 

                (drop table in 1)

            4) NEW PARTIAL DATA: get new data base on the last loaded data flag/id/date (partial data)

            5) concatenate partial data in (4)

                 with (1)

            6) STORE (5)

            • Re: Incremental Load
              shivesh singh

              Try this once

               

              LiveopsTelephonyData:

              LOAD [Campaign Name],

                  date([Start Date],'MM/DD/YYYY') as [Start Date],

                   [All Segments],

                   [Answered Segments],

                   [Average Time to Answer (minutes)],

                   [Call Length (minutes)],

                   [Average Call Length (minutes)],

                   [Queue Length (minutes)],

                   [Max Queue Length (minutes)],

                   Abandon,

                   [Abandon %],

                   [Caller Talk Time (minutes)]

              FROM

              $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD

              (qvd);

               

               

              RecentUpdate:

              Load  Max(date([Start Date],'MM/DD/YYYY')) as MaximumDate

              FROM

              $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

               

               

              Let vLastUpdatedDate = Peek('MaximumDate',0,'RecentUpdate');

               

               

              drop Table LiveopsTelephonyData;

               

               

              Incremental:

              LOAD [Campaign Name],

                    date([Start Date],'MM/DD/YYYY') as [Start Date],

                   [All Segments],

                   [Answered Segments],

                   [Average Time to Answer (minutes)],

                   [Call Length (minutes)],

                   [Average Call Length (minutes)],

                   [Queue Length (minutes)],

                   [Max Queue Length (minutes)],

                   Abandon,

                   [Abandon %],

                   [Caller Talk Time (minutes)]

              FROM

              $(vQVDPath_QlikMart_QVD)LiveOps.csv

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

              where date([Start Date],'MM/DD/YYYY') > $(vLastUpdatedDate);

               

               

              Concatenate

              Load

              [Campaign Name],

                   date([Start Date],'MM/DD/YYYY') as [Start Date],

                   [All Segments],

                   [Answered Segments],

                   [Average Time to Answer (minutes)],

                   [Call Length (minutes)],

                   [Average Call Length (minutes)],

                   [Queue Length (minutes)],

                   [Max Queue Length (minutes)],

                   Abandon,

                   [Abandon %],

                   [Caller Talk Time (minutes)]

              FROM

              $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

               

               

              store Incremental into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

               

               

              drop Table Incremental;

               

               

               

              Is your data changing on basis of Start Date only?

              Here what do you mean by duplicates? you are getting duplicate dates in your final qvd?