8 Replies Latest reply: Nov 3, 2016 11:36 AM by Dinesh Reddy Seelam RSS

    Incremental Load not working please help

    Dinesh Reddy Seelam

      Hello All,

       

      Some how my first incremental load try is not working properly as expected, I don't know if I am missing some thing!

       

      In the 1st reload from DB am able to load 183755 rows after incremental some how its just need to insert update & delete records it is loading only 37470

       

      Before Incremental:

      Capture.PNG

      I can see only 1,83,755 rows after dumping everything in to excel

      After Incremental load:

       

      Capture2.PNG

      I can see only 37,470 rows after dumping everything in to excel

       

       

      Script for incremental:

       

      Cancelled:

      LOAD ORGANIZATION_CODE,

          %CUST_PO,

          ORDER_NUMBER,

          ORDER_LINE_NUMBER,

          ItemStyle,

          CancelledQty,

          CancelCode,

          [PO Agree],

          StyleCancelledDate,

          ORDER_CREATION_DATE

      FROM

      [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

      (qvd);

       

      Last_Updated_Date:

      LOAD DATE(MAX(StyleCancelledDate),'MM/DD/YYYY') AS MaxDate

      Resident Cancelled;

       

      let Last_Updated_Date  = DATE(peek('MaxDate',0,'Last_Updated_Date'),'MM/DD/YYYY');

       

      drop Table Cancelled;

       

      OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=SQLREAD;Data Source=TST;Extended Properties=""] (XPassword is PNBLVbVMMbYeWWVMRRMGTFA);

       

       

      Incremental:

      LOAD "ORGANIZATION_CODE",

          "CUST_PO_NUMBER" as %CUST_PO,

          "ORDER_NUMBER",

          "ORDER_LINE_NUMBER",

          "ITEM_STYLE" as ItemStyle,

          "CANCELLED_QUANTITY" as CancelledQty,

          "CANCEL_CODE" as CancelCode,

          "PO_AGREEMENT_NUMBER" as [PO Agree],

          date("CANCELLED_DATE" ) as  StyleCancelledDate,

          "ORDER_CREATION_DATE";

      SQL SELECT *

      FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

       

       

      Concatenate

       

      LOAD ORGANIZATION_CODE,

          %CUST_PO,

          ORDER_NUMBER,

          ORDER_LINE_NUMBER,

          ItemStyle,

          CancelledQty,

          CancelCode,

          [PO Agree],

          StyleCancelledDate,

          ORDER_CREATION_DATE

      FROM

      [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

      (qvd)where not Exists (%CUST_PO);

       

      Inner join

       

      LOAD "CUST_PO_NUMBER" as %CUST_PO;

      SQL SELECT "CUST_PO_NUMBER"

      FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V";

       

      STORE Incremental into [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd] (qvd);

      drop Table Incremental;

      Cancelled_Lines:

      LOAD ORGANIZATION_CODE,

          %CUST_PO,

          ORDER_NUMBER,

          ORDER_LINE_NUMBER,

          ItemStyle,

          CancelledQty,

          CancelCode,

          [PO Agree],

          StyleCancelledDate,

          ORDER_CREATION_DATE

      FROM

      [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

      (qvd);

       

      Thanks In Advance

        • Re: Incremental Load not working please help
          lakkyreddy devendar

          after Connecting to the database add below one and reload


          Incremental:

          LOAD "ORGANIZATION_CODE",

              "CUST_PO_NUMBER" as %CUST_PO,

              "ORDER_NUMBER",

              "ORDER_LINE_NUMBER",

              "ITEM_STYLE" as ItemStyle,

              "CANCELLED_QUANTITY" as CancelledQty,

              "CANCEL_CODE" as CancelCode,

              "PO_AGREEMENT_NUMBER" as [PO Agree],

              date("CANCELLED_DATE" ) as  StyleCancelledDate,

              "ORDER_CREATION_DATE";

          SQL SELECT *

          FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

          STORE Incremental into [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd] (qvd);

           

           

          Concatenate

           

          LOAD ORGANIZATION_CODE,

              %CUST_PO,

              ORDER_NUMBER,

              ORDER_LINE_NUMBER,

              ItemStyle,

              CancelledQty,

              CancelCode,

              [PO Agree],

              StyleCancelledDate,

              ORDER_CREATION_DATE

          FROM

          [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

          (qvd)where not Exists (%CUST_PO);

          • Re: Incremental Load not working please help
            Aurélien Martinez

            Hi,

             

            Use the not exists with two parameters.

             

            Incremental:

            LOAD "ORGANIZATION_CODE",

                "CUST_PO_NUMBER" as %CUST_PO,

              "CUST_PO_NUMBER" as %CUST_PO_EXIST,

                "ORDER_NUMBER",

                "ORDER_LINE_NUMBER",

                "ITEM_STYLE" as ItemStyle,

                "CANCELLED_QUANTITY" as CancelledQty,

                "CANCEL_CODE" as CancelCode,

                "PO_AGREEMENT_NUMBER" as [PO Agree],

                date("CANCELLED_DATE" ) as  StyleCancelledDate,

                "ORDER_CREATION_DATE";

            SQL SELECT *

            FROM XXHHF."XXHSP_DB_CANCELLED_LINES_V" where "CANCELLED_DATE" > TO_DATE('$(Last_Updated_Date)','MM/DD/YYYY');

             

             

            Concatenate

             

            LOAD ORGANIZATION_CODE,

                %CUST_PO,

                ORDER_NUMBER,

                ORDER_LINE_NUMBER,

                ItemStyle,

                CancelledQty,

                CancelCode,

                [PO Agree],

                StyleCancelledDate,

                ORDER_CREATION_DATE

            FROM

            [$(vFldrDataFiles)\HHF_Shipments\Shipments_Cancelledlines.qvd]

            (qvd)where not Exists (%CUST_PO_EXIST, %CUST_PO);