14 Replies Latest reply: May 22, 2018 12:34 AM by Shivesh Singh 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?

                • Re: Incremental Load
                  steve jones

                  This is my Data model , once i reload the new data , i see the numbers being doubled for the previous months.

                  Say for example if there are 20,000 july answered segments , once i reload data with yesterday's data i see 40000 for july.basically the numbers in the chart are getting double every time i run with new data .

                    • Re: Incremental Load
                      RODELL BASALO

                      in your CASE tab.. you have

                       

                      1) loaded the CASES (all)

                       

                      2) concatenate the (all) in your second load case

                      where CaseStatus = 'Closed' AND (CaseCloseDt >= '05/01/2017' or isnull(CaseCloseDt));

                       

                      3) and concatenate (all + 2) with

                      where len(ReportCreatedDt) > 3;

                       

                      that is why your CASES data compounded much as double.....

                        • Re: Incremental Load
                          steve jones

                          Every time i reload this doc Offered and Handled number keep growing even while source same qvd, can someone please let me know why?i just cannot understand whats wrong with my script.

                            • Re: Incremental Load
                              RODELL BASALO

                              //OLD DATA LOAD

                              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);

                               

                               

                               

                               

                               

                               

                              //GET THE LAST MAX DATE FROM THE OLD DATA

                              RecentUpdate:

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

                              //FROM

                              //$(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD(qvd);

                              Resident LiveopsTelephonyData;

                               

                               

                               

                               

                               

                               

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

                               

                               

                               

                               

                               

                               

                              //Load OLD data less than $(vLastUpdatedDate)

                              // YOU Should only load partial data to concat with the new updates

                              LiveopsTelephonyData_old:

                              NoConcatenate // no to concat with all old data

                               

                               

                              LOAD *

                              Resident LiveopsTelephonyData

                              WHERE

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

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                              drop Table LiveopsTelephonyData;

                               

                               

                              Incremental:

                              NOCONCATENATE 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); //LESS AND EQUAL TO MAX DATE

                               

                               

                              Concatenate(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)LiveopsTelephonyData.QVD(qvd);

                              resident LiveopsTelephonyData_old;

                               

                               

                               

                               

                              store Incremental into $(vQVDPath_QlikMart_QVD)LiveopsTelephonyData.QVD;

                          • Re: Incremental Load
                            Shivesh Singh

                            I m on personal edition, cannot open your file, but how month field is not present in our script. How are you deriving it..

                              • Re: Incremental Load
                                steve jones

                                Here is the attached file again along with Old data and New data , all i want is to set up incremental load. Every day i get new data with past 5 days data i need to do an incremental load to add only new data to the existing data.

                                Please help.

                                  • Re: Incremental Load
                                    RODELL BASALO

                                    Hi THere was an EXIT SCRIPT; enabled on your telephony load that is why your data is only getting the old data

                                     

                                    please see comments in script

                                    • Re: Incremental Load
                                      Shivesh Singh

                                      //

                                      //Liveopsold:

                                      //LOAD [Campaign Name],

                                      //     Date(Date#([Start Date],'MM/DD/YYYY'),'DD/MM/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 [Liveops old.csv]

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

                                      //

                                      //store Liveopsold into LiveopsTelephonyData.qvd

                                      //(qvd);

                                      //

                                      //exit SCRIPT

                                      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

                                      LiveopsTelephonyData.qvd

                                      (qvd);

                                      //exit SCRIPT

                                      MaxDate:

                                      load Date(max([Start Date]),'DD/MM/YYYY')as Max_Date

                                      Resident LiveopsTelephonyData;

                                       

                                       

                                      let vMax = Peek('Max_Date',0,MaxDate);

                                       

                                       

                                      drop table LiveopsTelephonyData;

                                       

                                       

                                      Liveopsnew:

                                      LOAD #, [Campaign Name],

                                           Date(date#([Start Date],'YYYY-MM-DD'),'DD/MM/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

                                      [..\Desktop\Liveops new.csv]

                                      (txt, codepage is 1252, embedded labels, delimiter is ',', msq) where Date(date#([Start Date],'YYYY-MM-DD'),'DD/MM/YYYY') >'$(vMax)';

                                       

                                       

                                      //exit SCRIPT

                                      Concatenate

                                      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

                                      LiveopsTelephonyData.qvd

                                      (qvd);

                                       

                                       

                                      store Liveopsnew into LiveopsTelephonyData.qvd

                                      (qvd);