Incremental reload

[#1]

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...?

[#2]

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

[#3]

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

[#4]

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

[#5]

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...

[Or]

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.

[#7]

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.

[johnw]

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;

[rwunderlich]

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

[#10]

Hi John,

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