Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Incremental reload

Hi,

I have a QlikView application which loads the data from a
oracle database...

SELECT * FROM DB."DATE_DIMENSION";

Is there a way to do an incremental update of the data (only reload new data)
without loading the entire data of the db.

How is it possible...?

62 Replies
Highlighted
Not applicable

Hi,

Yes, you can do the incremental load in db.

Pre-requirement - Should have a primary key / Composite key and date field.

3 types of Incremental load
1. Only Insertion in db
2. Insertion & updation in db
3. Insertion, Updation & Physical Deletion in db

Eg: P1 as Primary Key, D1 as Date Field

For Only Insertion:

XYZ:
Load *;
SQL Select a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load a,b,c from XYZ.QVD;

Store XYZ into XYZ.QVD;

For Insertion and updation

XYZ:
Load *;
SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);

For Insertion, updation and deletion:

XYZ:
Load *;
SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
InnerJoin
SQL Select P1 from xyz;

Regards

Rajesh

Highlighted
Not applicable


Rajesh Jeyaraman wrote:
Hi,
Yes, you can do the incremental load in db.
Pre-requirement - Should have a primary key / Composite key and date field.
3 types of Incremental load
1. Only Insertion in db
2. Insertion & updation in db
3. Insertion, Updation & Physical Deletion in db
Eg: P1 as Primary Key, D1 as Date Field
For Only Insertion:
XYZ:
Load *;
SQL Select a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load a,b,c from XYZ.QVD;
Store XYZ into XYZ.QVD;
For Insertion and updation
XYZ:
Load *;
SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
For Insertion, updation and deletion:
XYZ:
Load *;
SQL Select P1,a,b,c from xyz where D1>"Yesterday's Date"
Concatenate
Load P1,a,b,c from XYZ.QVD where NOT EXISTS(P1,P1);
InnerJoin
SQL Select P1 from xyz;
Regards
Rajesh<div></div>


In all your example you are verifying the data with the DATE("Yesterday's Date") field. Is there are any other way to do so if we don't have the date field. As I don't have the date field in my table. But I want to do the incremental load. Is that possible? Please let me know.

Thanks and Regards,

Rikab

Highlighted
Not applicable

Hi,

It is bit difficult to find the inserted or updated records without the date field.

In my previous mail the date field that i am pointing to record created or updated date field.

Regards

Rajesh

Highlighted
Not applicable

Thanks,

I was just wondering if there is a way to do the incremental update without checking from the db which data sets are already imported. But it seems that there is no way to get around this...

Highlighted
Specialist III
Specialist III


whiterabbit wrote:I was just wondering if there is a way to do the incremental update without checking from the db which data sets are already imported. But it seems that there is no way to get around this...


Answering a question with a question - QlikView aside, how would you query the table only for records that were not previously imported, e.g. records that have changed (or been added) since the last time you run the query? If there's any field you can use that indicates this, you can use that for QlikView as well - but if your data does not in any way reflect changes, there's no way for QlikView to know what needs to be updated.

One thing you can do instead of an incremental update is to load X days back and concatenate that with the contents of a QVD - assuming your records stop changing after a certain amount of time, of course. For example:

LET DATE_START = DATE((TODAY()-30));

myData:

SELECT * FROM myTable WHERE CreateDate>='$(DATE_START)';

Load * FROM myQVD.qvd WHERE CreateDate < '$(DATE_START)';

store * myData into myQVD.qvd;

Hope this helps.

Highlighted
Not applicable

Now I added a datefield "MODTIME" which containts the timestamp when the data is created as value.
In the loadscript of QV app I have:

...

LET LastExecTime = ReloadTime();


myData:
SELECT * FROM ORDERS
WHERE MODTIME > TO_TIMESTAMP('$(LastExecTime)','MM/DD/YY HH:MI:SS');
LOAD * FROM myQVD.qvd;
store * from myData into myQVD.qvd;

When I reload the data, the values stored in the qvd file are not loaded correctly (they are not shown, only the data added in the db is shown). Instead of displaying the data of myQVD there appears a field with xml. What I am doing wrong in the above statements??

Moreover, the first time I executed it, there was also an error because the qvd file does not yet exist... How can I handle this.

Highlighted
MVP
MVP

Something like this (untested, probably a couple syntax errors at a minimum):

IF filesize('myQVD.qvd') > 0 THEN
MaxTimeTable:
LOAD max(MODTIME) as MaxTime FROM myQVD.qvd (QVD);
MyData:
LOAD *;
SQL SELECT * FROM ORDERS
WHERE MODTIME > TO_TIMESTAMP($(=fieldvalue('MaxTime',1)),'MM/DD/YY HH:MI:SS');
DROP TABLE MaxTimeTable;
CONCATENATE (MyData) LOAD * FROM myQVD.qvd (QVD);
ELSE
MyData:
LOAD *;
SQL SELECT * FROM ORDERS;
END-IF

STORE MyData INTO myQVD.qvd;

Highlighted
MVP & Luminary
MVP & Luminary


whiterabbit wrote:Instead of displaying the data of myQVD there appears a field with xml


Because the (qvd) parameter is missing in the QVD load.

LOAD * FROM myQVD.qvd (qvd)

-Rob

Highlighted
Not applicable

Hi John,

Can you please attach any sample application which does he incremental load please.