6 Replies Latest reply: May 17, 2017 5:06 AM by Rex Liao RSS

    Load Incremental data to QVD, but how come it causes past data deleted?

    Rex Liao

      Dear all,

      The followings are what I am trying to do.

      (1) Test_Data_qvf:Load "Position_Historical" data as of 2017/5/2 from "Test_DB.accdb" into "Position_Historical_QVD".

      (2) Test_Data_qvf:Load Incremental "Position_Historical" data as of 2017/5/3 from "Test_DB.accdb" into "Position_Historical_QVD".

      (3) Test_Structure_qvf:Load "Position_Historical" data from "Position_Historical_QVD".

       

      If I run (1) and (3), the data can be loaded perfectly.

      However, if I run (1), (2) and (3), the data as of 2017/5/3 cannot be loaded, and data as of 2017/5/2 is also deleted.

      Could anyone advise what it's wrong in the following script?

       

      ===== [Script in Test_Data_qvf] =====

      LIB CONNECT TO 'Test_DB';


      //(1)Load Data as of 2017/5/2 to QVD
      LET DataDate = Date(Date#('2017/5/2','YYYY/MM/DD'),'MM/DD/YYYY');

      LOAD `Client_ID`,
          `Loan_USD`,
          `Market_Value_USD`,
          `Report_Date`,
          `Security_ID_BLG`;
      SQL SELECT `Client_ID`,
          `Loan_USD`,
          `Market_Value_USD`,
          `Report_Date`,
          `Security_ID_BLG`
      FROM `Position_Historical`
      WHERE Report_Date=#$(DataDate)#;
      STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);

       


      //(2)Load Incremental Data as of 2017/5/3 to QVD
      LET DataDate = Date(Date#('2017/5/3','YYYY/MM/DD'),'MM/DD/YYYY');

      QV_Table:
          LOAD `Client_ID`,
              `Loan_USD`,
              `Market_Value_USD`,
              `Report_Date`,
              `Security_ID_BLG`;
          SQL SELECT `Client_ID`,
              `Loan_USD`,
              `Market_Value_USD`,
              `Report_Date`,
              `Security_ID_BLG`
          FROM `Position_Historical`
          WHERE Report_Date=#$(DataDate)#;

          NoConcatenate LOAD
              `Client_ID`,
              `Loan_USD`,
              `Market_Value_USD`,
              `Report_Date`,
              `Security_ID_BLG`
          FROM [lib://Test_QVD/Position_Historical.QVD](QVD)
          WHERE NOT EXISTS(Report_Date);

      STORE QV_Table INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
      DROP TABLE QV_Table;

      ===========================================================

        • Re: Load Incremental data to QVD, but how come it causes past data deleted?
          sanjay gupta

          Hi Rex !

           

          I have updated your script plz check

           

           

          ===== [Script in Test_Data_qvf] =====

          LIB CONNECT TO 'Test_DB';

           

          Record:

          LOAD `Client_ID`,

              `Loan_USD`,

              `Market_Value_USD`,

              `Report_Date`,

              `Security_ID_BLG`;

          SQL SELECT `Client_ID`,

              `Loan_USD`,

              `Market_Value_USD`,

              `Report_Date`,

              `Security_ID_BLG`

          FROM `Position_Historical`;

          STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);

           

          update_Record:

          LOAD*

          Resident Record

          Order by Report_Date;

           

           

          LET vUpdated_DateHeader= Peek('Report_Date',-1,'update_Record');

           

           

          DROP Table Record;

           

           

          LIB CONNECT TO 'Test_DB';

          test:

          LOAD `Client_ID`,

              `Loan_USD`,

              `Market_Value_USD`,

              `Report_Date`,

              `Security_ID_BLG`;

              Where Report_Date>='$(vUpdated_DateHeader)';

             

          SQL SELECT `Client_ID`,

              `Loan_USD`,

              `Market_Value_USD`,

              `Report_Date`,

              `Security_ID_BLG`

             FROM `Position_Historical`;

           

          Concatenate

           

          LOAD `Client_ID`,

              `Loan_USD`,

              `Market_Value_USD`,

              `Report_Date`,

              `Security_ID_BLG`;

          FROM [lib://Test_QVD/Position_Historical.QVD]

          (qvd)

          Where not Exists (Client_ID);