Incremental Load Problem

[#1]

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;

[#2]

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;

[#3]

Ok I've implemented this and will wait and see the results tomorrow. Out of interest (and the same question for mohit) how would the slight changes affect the incremental load?

Thanks,

Ralph

[#4]

Unfortunately none of the above solutions have worked so I'm a bit perplexed now. Any other suggestions or alternative incremental approaches, bearing in mind there would be multiple transactions on a timestamp.

Thanks,

Ralph