4 Replies Latest reply: Apr 27, 2016 11:47 AM by Riley MacDonald RSS

    Overwriting Rows and Incremental Load

    Riley MacDonald

      Hi,

       

      I have the following table which will be refreshed daily and will contain years of daily data which means ~30 million records:

       

      table1:

      SKUDayKeyQty
      A01/01/2016A01/01/201610
      A02/01/2016A02/01/20165
      A03/01/2016A03/01/20163

       

      I want to load all of the historical data from my database into a QVD and then everyday take the last 3 days of data from my database and add any new records (which I can determine by the Key field) and then if a record comes in during the last 3 days load that already has an existing key in the historical QVD I would like to overwrite that record. I need to do this as the data in the column Qty can change historically over the last 3 days. Can anyone help with this?

       

      Thanks,

      R

        • Re: Overwriting Rows and Incremental Load
          Nicole Smith

          Here is some "pseudo" code to hopefully get you started...

           

          /* Check if QVD exists, if not load all history from DB */

          IF FileTime(YourQVD.qvd)>0 THEN

               /* Load last 3 days from DB */

               LET vMinDate = date(today()-3);

               YourTable:

               SELECT *

               FROM YourDatabaseTable

               WHERE YourDateField >= $(vMinDate);

           

               /* Load only things not updated from QVD */

               CONCATENATE (YourTable)

               LOAD *

               FROM YourQVD.qvd

               WHERE not exists(YourKeyField);

             

          ELSE

               /* Load everything from database */

               YourTable:

               SELECT *

               FROM YourDatabaseTable;

          ENDIF

           

          STORE * FROM YourTable INTO YourQVD.qvd (qvd);

          • Re: Overwriting Rows and Incremental Load
            Riley MacDonald

            This works fine for my first case of only adding the new records, however how do I deal with records that need to be overwritten? In this case you could imagine my key of SKU&Day would exist in the stored QVD already but the Qty would be different and therefore I would need to take this record and add it into my stored QVD and ensure the old record is overwritten with the new one.