4 Replies Latest reply: Dec 20, 2017 2:49 AM by Anil Samineni RSS

    Incremental load issue

    Amit Saini

      Hi Folks,

       

      I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:

       

      if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

       

        trace >>>>>> $(vQVD)\CAQ_ABC.qvd    ;

       

      MAX_TIME:

        load

           max(DTTSERF) as LAST_LOAD_TIME

        from $(vQVD)\CAQ_ABC.qvd (qvd)

        ;

       

        let vLAST_LOAD_TIME =  timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');

       

        trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

       

        drop table MAX_TIME;

       

      else

       

        LET vLAST_LOAD_TIME = '$(vStartDate)';

        trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

       

      end if

       

       

      // load new values

      CAQ_ABC:

      LOAD "Facility_Id",

          Date,

          Product,

          Variant,

          Station,

          Serial,

          Characteristic,

          "Values",

          Status,

          DTTSERF;

      SQL SELECT *

      FROM MasterCAQ.dbo."CAQ_ABC"

      WHERE DTTSERF > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS')

      ;

       

      // load prior values

       

      if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

       

      concatenate(CAQ_ABC)

      load

      *

      from $(vQVD)\CAQ_ABC.qvd (qvd);

       

      end if

       

      // store all values

       

      store CAQ_ABC into $(vQVD)\CAQ_ABC.qvd;

       

      LET vCAQ_ABC_LOAD_DURATION = Interval(now() - vStart) ;

      LET vStart = now();

       

      LET vCAQ_ABC_Size = num(filesize('$(vQVD)\CAQ_ABC.qvd')/1024,'00.0') & ' KB';

      LET vCAQ_ABC_Records = QvdNoOfRecords ('$(vQVD)\CAQ_ABC.qvd');

       

       

      drop table CAQ_ABC;

       

      Regards,

      AS

       

      Message was edited by: Amit Saini

        • Re: Incremental load issue
          Anil Samineni

          Amit Saini wrote:

           

          Hi Folks,

           

          I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:

           

          MAX_TIME:

            load

               max(DTTSERF) as LAST_LOAD_TIME

            from $(vQVD)\CAQ_ABC.qvd (qvd)

            ;

           

            let vLAST_LOAD_TIME =  timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');

           

            trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

           

            drop table MAX_TIME;

           

          else

           

            LET vLAST_LOAD_TIME = '$(vStartDate)';

            trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

           

          end if

          Is there any IF statement for this? If this is the full script you have Else won't work. Could be reason..

            • Re: Incremental load issue
              Amit Saini

              Sorry Anil,

               

              Forgot below lines

               

              if filesize('$(vQVD)\CAQ_ABC.qvd') >0  then

               

                trace >>>>>> $(vQVD)\CAQ_ABC.qvd    ;

               

              and than it starts as per above comments.

               

              But in general is there any way to do incremental load based on last reload time for below example:

               

              CAQ_ABC:

              LOAD "Facility_Id",

                  Date,

                  Product,

                  Variant,

                  Station,

                  Serial,

                  Characteristic,

                  "Values",

                  Status,

                  DTTSERF;

              SQL SELECT *

              FROM MasterCAQ.dbo."CAQ_ABC";

               

              Thanks,

              AS

                • Re: Incremental load issue
                  Anil Samineni

                  May be this?

                   

                  CAQ_ABC:

                  LOAD "Facility_Id",

                      Date,

                      Product,

                      Variant,

                      Station,

                      Serial,

                      Characteristic,

                      "Values",

                      Status,

                      DTTSERF;

                  SQL SELECT * FROM MasterCAQ.dbo."CAQ_ABC";

                   

                  Store CAQ_ABC into [FilePath...];

                  Drop Table CAQ_ABC;

                  Sample:

                  Load * From <Your QVD>;

                  Last_Update_Reload:

                  Load Max(Date) as Max_Date Resident Sample;

                  Let VarForMax = Peek('Max_Date', 0, 'Last_Update_Reload');

                  Drop Table Sample;

                   

                  In future, The Date may extend from current Max date to till next 2 Years. That means this?

                   

                  First:

                  Load * From Sample_External_Source Where Date > $(VarForMax);

                  Concatenate

                  Load * From <Sample Qvd Path>;

                  Store First into ...;

                  Drop Table First;

                  Now, Load Fresh Table into QVD which is Last one

                  Load * From <First>;