Help with first go at incremental loads and updates

Anonymous — Tue, 18 Feb 2014 22:08:57 GMT

Ok, I am working on improving our dataset from the last 3 full months to last 12 full months of incidents and decided to go the incremental load and update route. I am struggling with a few things though.

First, I did the initial load:

Inc_Table:
SELECT incident.number as incident_number,
incident.data_updated as data_updated
FROM tbl_incident incident
WHERE
incident.opened_at >= convert(varchar(10),dateadd(mm,-12,dateadd(dd,-(day(getdate())-1),getdate())),101);

STORE Inc_Table INTO incidents.QVD;

LET LastExecTime = now();

After the initial load I added the below code to my WHERE statement for Inc_Table query.

and incident.data_updated >= '$(LastExecTime)'

Then I added the below concatenate between Inc_Table populate and STORE (before store in order to concat old records where incident_number doesn't exist in the new Inc_Table, correct?)

Concatenate
LOAD incident_number,
      data_updated
FROM
[incidents.QVD]
(qvd)
WHERE NOT EXISTS(incident_number);

So, where do I need help:

The Inc_Table query is fetching a number of rows that is no where near what it should. I've tested the query with a manually typed LastExecTime using DBArtisan and I get ~40 rows which is accurate but Qlikview always returns ~1,300 lines fetched. No idea what is going on with this. Assuming something with LastExecTime.
How do I change my data_updated to query max data_updated from QVD and is that a better way to do it?
Also, is the method I am using correct to query newly updated records and merge with QVD records?

Re: Help with first go at incremental loads and updates

Tue, 18 Feb 2014 22:54:56 GMT

Please check out the attached folder. It has a file with a working example and explanation of how incremental load should be coded/used.Thanks to Rob Wunderlich for sharing this.

This should answer all your questions

topic Help with first go at incremental loads and updates in QlikView

Help with first go at incremental loads and updates

Re: Help with first go at incremental loads and updates