3 Replies Latest reply: Jul 11, 2012 12:07 PM by Some Nath Roy RSS

    append, or incremental load for QVD

    Dan-Ketil Jakobsen

      Hi

       

      I am trying to understand use of QVD.

       

      I have created a QVD with this code:

       

      let vCIPStepQVD = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-01-01 00:00:00' & chr(39) & ','  & '@EndTime = ' & chr(39) & '2012-07-09 23:59:59' & chr(39);

       

      CIPStepQVD:

      LOAD * ;

       

      SQL $(vCIPStepQVD);

       

      Store CIPStepQVD INTO [X:\QlikView Storage\Private\Test\QVD\Data.qvd];

       

      Now I need to add data on an hourly time schedule, and wonder if this will work:

       

      let vCIPStepQVD = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & reloadtime  & chr(39) & ','  & '@EndTime = ' & chr(39) & Now()   & chr(39);

       

      CIPStepQVD:

      LOAD * ;

       

      SQL $(vCIPStepQVD);

       

      Store CIPStepQVD INTO [X:\QlikView Storage\Private\Test\QVD\Data.qvd];

       

       

      The idea is that it should use last reloadtime() as start point each time and have Now() as end point.

      But I am not familiar with QVD. Would this code append the current data to the original QVD or will it ereas it and replace it with the new data?

       

       

      BR

      Dan

        • Re: append, or incremental load for QVD
          Some Nath Roy

          Hi,

           

          Store CIPStepQVD INTO [X:\QlikView Storage\Private\Test\QVD\Data.qvd]; 

          The above command overwrites the existing Data.qvd file.

           

          Thus before executing the SQL, you need to check whether the this initial ( first time) execution or incremental execution.

          You can check this by NoOfRows('[Table Name]') function after LOADing ..\Data.qvd file.

           

          If INITIAL then

              

          CIPStepQVD:

          LOAD * ;

           

          SQL $(vCIPStepQVD);

           

          Store CIPStepQVD INTO [X:\QlikView Storage\Private\Test\QVD\Data.qvd];

           

          else

              

          Concatenate(CIPStepQVD)

          LOAD * ;

           

          SQL $(vCIPStepQVD);

           

          Store CIPStepQVD INTO [X:\QlikView Storage\Private\Test\QVD\Data.qvd];

           

           

          regards,

          Som

            • Re: append, or incremental load for QVD
              Dan-Ketil Jakobsen

              Hi Som

               

              Thanks for reply. I am trying to understand what you wanted me to do.

              I have created a new app and here is what I thought you asked me to test:

               

              **************

              let vCIPStepQVD1 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 00:00:01' & chr(39) & ','  & '@EndTime = ' & chr(39) & '2012-07-10 06:00:00' & chr(39);

              let vCIPStepQVD2 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 06:00:01' & chr(39) & ','  & '@EndTime = ' & chr(39) & '2012-07-10 07:00:00' & chr(39);

               

              //created vCIPStepQVD1 to be the intial load code. vCIPStepQVD2 is the increment code.

              //Use smaler timespann otherwise it would take for ever to load.

               

              FaseHistory:

              LOAD*

              FROM

              [X:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd]

              (qvd);

               

              LET NoOfRows =noofrows('FaseHistory');

               

              IF NoOfRows >= 1 THEN

               

              CIPStepQVD1:

              LOAD

              //I insert a loadtime field as to tell at waht time it was loaded.

              '2012-07-10 06:00:00' as LoadTime,*;

               

              SQL $(vCIPStepQVD1);

               

              Store CIPStepQVD1 INTO [D:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd];

               

              ELSEIF

               

              Concatenate(CIPStepQVD2)

              LOAD

              '2012-07-10 07:00:00' as LoadTime,*;

               

              SQL $(vCIPStepQVD2);

               

              Store CIPStepQVD2 INTO [X:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd];

              ENDIF

               

              **************

               

              But I get the following error:

               

              Table not found

              Store CIPStepQVD1 INTO [D:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd]

               

              It seem to be something with the concatenate part of the code.

               

              Regards

              Dan

                • Re: append, or incremental load for QVD
                  Some Nath Roy

                  Pls. try the below code:

                   

                  let vCIPStepQVD1 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 00:00:01' & chr(39) & ','  & '@EndTime = ' & chr(39) & '2012-07-10 06:00:00' & chr(39);

                  let vCIPStepQVD2 = 'EXEC dbo.sp_CIPSteps @StartTime= ' & chr(39) & '2012-07-10 06:00:01' & chr(39) & ','  & '@EndTime = ' & chr(39) & '2012-07-10 07:00:00' & chr(39);

                   

                  //created vCIPStepQVD1 to be the intial load code. vCIPStepQVD2 is the increment code.

                  //Use smaler timespann otherwise it would take for ever to load.

                   

                  FaseHistory:

                  LOAD*

                  FROM

                  [X:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd]

                  (qvd);

                   

                  LET NoOfRows =noofrows('FaseHistory');

                   

                  IF NoOfRows < 1 THEN

                   

                  CIPStepQVD1:

                  LOAD

                  //I insert a loadtime field as to tell at waht time it was loaded.

                  '2012-07-10 06:00:00' as LoadTime,*;

                   

                  SQL $(vCIPStepQVD1);

                   

                  Store CIPStepQVD1 INTO [D:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd];

                   

                  ELSEIF

                   

                  Concatenate(FaseHistory)  //Changed line

                  LOAD

                  '2012-07-10 07:00:00' as LoadTime,*;

                   

                  SQL $(vCIPStepQVD2);

                   

                  Store FaseHistory INTO [X:\QlikView Storage\Private\Production\UtviklingTest_Dan\QVD\MES_CIPSteps_Data2.qvd];  //Changed line

                  ENDIF