Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nicklisterman
Contributor

Help with first go at incremental loads and updates

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:

  1. 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.
  2. How do I change my data_updated to query max data_updated from QVD and is that a better way to do it?
  3. Also, is the method I am using correct to query newly updated records and merge with QVD records?
1 Reply
Not applicable

Re: Help with first go at incremental loads and updates

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

Community Browser