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

Rajesh Jeyaraman:

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

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

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

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

whiterabbit:

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.

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.

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;

whiterabbit:

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

Hi John,

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

Hi Rob,

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

Rikab Kothari:

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

There is an example in the Qlikview Cookbook.

-Rob

Hi Rob,

Here is the attached application for incremental load. plz find it.

Hope it will solve your problem.

Thanks

Neetu Singh

Hi Neetu,

It was not Rob. It was me who was looking for the incremental reload.

Anyway many thanks for sending me the application. Hope it is understandable and can use the same logic in my document. Because I have few document which does the incremental load but I couldn't understand how it was done. Hope your document will be understandable.

Rikab Kothari:

It was me who was looking for the incremental reload

Hi Rikab,

There is another example in the Qlikview Cookbook that takes a slightly different approach then Neetu's example. You can download the Cookbook from

http://robwunderlich.com/Download.html

-Rob