topic Re: Incremental Load Problem in QlikView

Incremental Load Problem

Thu, 08 Aug 2013 08:53:28 GMT

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

Thu, 08 Aug 2013 08:58:53 GMT

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;

Re: Incremental Load Problem

Thu, 08 Aug 2013 09:32:41 GMT

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

Re: Incremental Load Problem

Mon, 12 Aug 2013 07:26:00 GMT

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