3 Replies Latest reply: Feb 7, 2014 11:23 AM by Michael Solomovich RSS

    Incremental Load Based on Variable Date

      I am attempting to create an incremental load where the data from the file (TestData.csv) is loaded into TestDataQVD.qvd only if the "DATE" is greater than the variable date vMAXDATE (based on the max date of the qvd).  Script is below and example case is attached.  I've already performed an initial load, so there is one piece of data in the qvd file.  The .csv file has two rows of data, one of which should load since the DATE (1/24/2014) is greater than vMAXDATE (1/10/2014).  I've tried multiple ways of converting the the "DATE" field to match the variable format without success.

       

      //Finding the max date value in the QVD

      MAX_DATE:

      LOAD

           MAX(DATE) AS [MAX_DATE]

       

      FROM TestDataQVD.qvd (qvd)

      ;

       

      //Setting the variable value to the "MAX_DATE" field determined in previous step

      SET vMAXDATE = MAX_DATE

      ;

       

      //Loading data currently in the QVD

      TEST_DATA:

      LOAD DISTINCT

            *

       

      FROM TestDataQVD.qvd (qvd)

      ;

       

      //Loading the new data from the CSV file, concatenating it to the QVD date

      LOAD DISTINCT

           ID,

           DATE,

           DEDCD,

           DED_AMT

       

      FROM TestData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

       

      //Only load where the DATE is greater than the variable vMAXDATE

      WHERE DATE > '$(vMAXDATE)'

      ;

       

      //Storing all of the data (QVD + CSV) in the QVD

      STORE TEST_DATA


      INTO TestDataQVD.qvd (qvd)

      ;

      //Reloading the data from the QVD for verification

      LOAD

           *

       

      FROM TestDataQVD.qvd (qvd)

      ;