topic Incremental reload in QlikView

Incremental reload

Wed, 21 Apr 2010 08:03:48 GMT

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

Incremental reload

Wed, 21 Apr 2010 08:22:53 GMT

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

Incremental reload

Wed, 21 Apr 2010 08:39:05 GMT


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

Incremental reload

Wed, 21 Apr 2010 08:58:42 GMT

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

Incremental reload

Thu, 22 Apr 2010 09:50:43 GMT

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

Incremental reload

Or — Thu, 22 Apr 2010 10:12:10 GMT


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.

Incremental reload

Fri, 23 Apr 2010 12:51:10 GMT

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.

Incremental reload

johnw — Sat, 24 Apr 2010 00:17:41 GMT

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;

Incremental reload

rwunderlich — Sat, 24 Apr 2010 04:59:38 GMT


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

Incremental reload

Thu, 10 Jun 2010 07:16:59 GMT

Hi John,

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

Incremental reload

Thu, 10 Jun 2010 07:20:32 GMT

Hi Rob,

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

Incremental reload

rwunderlich — Tue, 06 Jul 2010 22:29:24 GMT


Rikab Kothari wrote:Can you please attach any sample application which does he incremental load please.

There is an example in the Qlikview Cookbook.

-Rob

Incremental reload

neetu_singh — Fri, 09 Jul 2010 12:57:48 GMT

Hi Rob,

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

Hope it will solve your problem.

Thanks

Neetu Singh

Incremental reload

Fri, 09 Jul 2010 13:45:02 GMT

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.

Incremental reload

rwunderlich — Fri, 09 Jul 2010 22:27:02 GMT


Rikab Kothari wrote: 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

Incremental reload

Sat, 10 Jul 2010 05:54:18 GMT

Hi Rob,

Thanks for reply! Yes I already gone through that. But not able to understand how it works. If I don't understand then it not easy to apply the same logic in my document also.

May be I need to dig little deeper to find out how exactly it is happening there. I will definitely go through it once more then will let you know the outcome. Again many thanks for your concern!

Incremental reload

Sat, 10 Jul 2010 06:15:28 GMT

Hi Rob and All(Who are all is involved in this post),

I have attached very basic document to work on with incremental load. Can some one please do the incremental load with that so that I can understand the basic things in that.

It is very small and it will not take much time to work on it.

Hope to receive reply from some one soon.

Incremental reload

Sat, 17 Jul 2010 05:55:47 GMT

Hi All,

Can some one please work on in my document and help me out!

Thanks and Regards,

Rikab Kothari

Incremental reload

Mon, 19 Jul 2010 05:30:15 GMT

Hi All,

Can some please help me out to do the incremental load for the document which is attached just for the understanding purpose. I have been waiting for the long time but still there is no response.

Incremental reload

neetu_singh — Mon, 19 Jul 2010 08:40:41 GMT

Hi Rikab,

Please find the attached application for incremental load. hope it will helpful to you.

If any issue . Plz feel free to coordinate with me,

Bye nd Take Care

Neetu Singh