Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
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