3 Replies Latest reply: Aug 12, 2013 3:26 AM by Ralph Graham RSS

    Incremental Load Problem

      Hello,

       

      I am encountering an issue with my incremental load where it seems that not all data is being captured, i.e. I've ran a query for 1 day and compared to the incremental load qvd and there are more transactions in the query. And there is no issue with data being retrospectively changed as it is a purely transactional factory system. The problem is also exaggerated as the incremental job runs every hour so over the course of the day there can be quite a difference at times. My script is below and I just don't see what the issue could be.

       

      Thanks in advance,

       

      Ralph

       

      maxdateTab:

               
      LOAD max(Timestamp(StartTime,'YYYY-MM-DD
      hh:mm:ss'))
      as maxdate
           FROM $(vdatasource)Factory.qvd (qvd);

          
      LET vIncrementalExpression = peek('maxdate');

          
      DROP table maxdateTab;

      TotalFactory:
      SQL SELECT

           Line,

           ID,

           Factory,
           Product,
           starttime       AS 'StartTime',
           Weight

      FROM
      Table1

      WHERE

           Factory=’SiteA’

      and starttime >= '$(vIncrementalExpression)'   

      Concatenate

      Load *

      from $(vdatasource)Factory.qvd (qvd)


      where not Exists (ID);




      store TotalFactory into $(vdatasource)Factory.qvd (qvd);


      drop table TotalFactory;

        • Re: Incremental Load Problem
          Vishwaranjan Kumar

          try this

           

           

          maxdateTab:

                   
          LOAD max(Timestamp(StartTime,'YYYY-MM-DD
          hh:mm:ss'))
          as maxdate
               FROM $(vdatasource)Factory.qvd (qvd);

              
          LET vIncrementalExpression = peek('maxdate',0,'
          maxdateTab');

              
          DROP table maxdateTab;

          TotalFactory:
          SQL SELECT

               Line,

               ID,

               Factory,
               Product,
               starttime       AS 'StartTime',
               Weight

          FROM
          Table1

          WHERE

               Factory=’SiteA’

          and starttime >= '$(vIncrementalExpression)'    ;

          Concatenate

          Load *

          from $(vdatasource)Factory.qvd (qvd);


          where not Exists (ID);




          store TotalFactory into $(vdatasource)Factory.qvd (qvd);


          drop table TotalFactory;