12 Replies Latest reply: Mar 25, 2014 3:22 AM by sunil kumar RSS

    Incremental Load

    Manish Kachhia

      I have sales file for which I need to do incremental load.

       

      While doing incremental Load, need to consider NEW RECORDS as well as update in existing record’s Despatched Column.

       

      Sample file enclosed. Let me know what is wrong in this script… There are two worksheet in excel file...

        • Re: Incremental Load
          Srikanth P

          I suspect there is date format problem in the excel file.Please find below some points & sample script

           

          -> For Max Date, load only particular field like below.

          -> Please make sure that QVD exist or not. If QVD exist do Incremental Load else do Full Load from source

          -> Please make sure that date formats should be same in source & variable.

           

          Script:

           

          Let vQVDExist = IF(FILESIZE('C:\Incremental Load\Sales.qvd')>0,-1,0)

           

           

          IF vQVDExist THEN // Inc Load

            Max_DT:

            LOAD Max([Invoice Date]) AS MAX_DT,

            FROM [C:\Incremental Load\Sales.qvd] (qvd);

           

           

            Let vMaxInvoiceDate = Num(Peek('MAX_DT'));

           

           

            DROP Table Max_DT;

           

           

            Incremental:

            NoConcatenate

            LOAD [Customer ID],

            [Customer Name],

            [Invoice No],

            [Invoice Date],

            [Part Number],

            QTY,

            Despatched

            FROM [C:\Incremental Load\Sales.xlsx] (ooxml, embedded labels, table is Update)

            Where Num([Invoice Date]) >= $(vMaxInvoiceDate); //Date interpretaion is need to be check. please make sure that both sides needs to be same format. If the date is text in the excel, please convert into date number.

           

           

            Concatenate

            LOAD [Customer ID],

            [Customer Name],

            [Invoice No],

            [Invoice Date],

            [Part Number],

            QTY,

            Despatched

            FROM [C:\Incremental Load\Sales.qvd] (qvd)

            Where Not Exists ([Invoice No]);

            Store Incremental into 'C:\Incremental Load\Sales.qvd'(QVD);

           

           

            Drop Table Incremental;

          ELSE //Full Load

            Full:

            LOAD [Customer ID],

            [Customer Name],

            [Invoice No],

            [Invoice Date],

            [Part Number],

            QTY,

            Despatched

            FROM [C:\Incremental Load\Sales.xlsx] (ooxml, embedded labels, table is Update);

           

            Store Full into 'C:\Incremental Load\Sales.qvd'(QVD);

           

           

            Drop Table Full;

          ENDIF