1 Reply Latest reply: Sep 26, 2017 2:01 PM by Anand Chouhan RSS

    Incremental Loads and Concatenated QVD file

    Stevan Townsend

      Is there a way to load & concatenate multiple QVD files and do an incremental load on only one of the QVD files

      I have a 2015.qvd, a 2016.qvd file, and a 2017.qvd file. The 2015 & 2016 are static, but the 2017 needs to be updated daily. With the code below when I STORE the 2017, it duplicates the data from 2015 & 2016 in the 2017 file.


      [Stats]:

      LOAD *

      from 'lib://QVD (folder)/Stats_2015.qvd' (qvd);

      concatenate

      LOAD *

      from 'lib://QVD (folder)/Stats_2016.qvd' (qvd);

      concatenate

      LOAD *

      from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);

      CONCATENATE

      LIB CONNECT TO 'DBConnection';

      LOAD .......;

      SQL *

      FROM Data.dbo.Stats

      where convert(int,Startdate) > $(Last_Update_Date);

       

      Last_Update_Date:

      LOAD Max(StartDate) as MaxDate

      Resident [Stats];

      Let Last_Update_Date=Peek('MaxDate',0,'Last_Update_Date');

       

      Store Stats into 'lib://QVD (folder)/Stats_2017.qvd';



        • Re: Incremental Loads and Concatenated QVD file
          Anand Chouhan

          Try to create single qvd of 2015,2016 as you say they are static then you need to just concatenate this qvd at the last of the incremental load. And incremental load takes place only in the 2017 file with mas date logic.


          //MAX DATE FINDING

          Last_Update_Date:

          LOAD Max(StartDate) as MaxDate

          from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);

           

          Let Last_Update_Date=Peek('MaxDate',0,'Last_Update_Date');

           

          //INCREMENTAL LOAD START

          LIB CONNECT TO 'DBConnection';

          [Stats_2017]

          LOAD .......;

          SQL *

          FROM Data.dbo.Stats

          where convert(int,Startdate) > $(Last_Update_Date);


          concatenate(Stats_2017)

          LOAD *

          from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);


          Store Stats into 'lib://QVD (folder)/Stats_2017.qvd';

          Drop table Stats_2017;


          [Stats]

          LOAD *

          from 'lib://QVD (folder)/Stats_2017.qvd' (qvd);

           

          concatenate(Stats)

          LOAD *

          from 'lib://QVD (folder)/Stats_2015.qvd' (qvd);

           

          concatenate(Stats)

          LOAD *

          from 'lib://QVD (folder)/Stats_2016.qvd' (qvd);