Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental Load Problem!

Hi All,

I tried to do the incremental load with the help of the document which is given in the below link.

http://community.qlik.com/media/p/125837.aspx

But as per me it is not working . Requesting you to let me know what changes I need to do so that it works as expected.

Explanation about the problem given below:

~~~~~~~~

I tried the incremental load application with the data of mine. I am facing few issues when I was trying with my data. It is not working as expected. Explained about the problem in the screen shot which is given below. Hope you can look into it and provide some solution for the same.

error loading image

error loading image

I have attached the document which I was trying with.

~~~~~~~~

Thanks and Regards,

Rikab Kothari

44 Replies
Not applicable
Author

Hi Sunil,

As our implementation are not well processed chances are there there may so several wrong entries. So we tend to delete all those wrong entries from the back end instead of doing it from the front end. So in that case I wish Rob's method should handle deletes also.

So in my case if the incremental load doesn't handle deletes then there will be lots of unnecessary records will be available in the document.

suniljain
Master
Master

Dear Rikab,

suppose I have One Record Which have primary key as p1 . If I delete that record in your scenario and Add new record inmplace of that record what will be primary key ?.

Not applicable
Author

Hi Rikab,

Rob's incremental load file does not handle deleted records. We cannot handle the deletion of deleted records unless we have that information stored somewhere.

Say we have deleted 300 rows today in fact table and you want to delete these records from the QVD in the next reload. This can't be done in the existing script since, there is no reference for these deleted records in the database.

This can be done in the following ways,

1. Instead of deleting the unwanted rows, update those rows with '0' or null as value for the fact fields. So, our incremental reload script will identify these update and will load the latest value into the QVD. This option is not efficient since we will have to load rows with no values and this will increase the QVD/document size.

2. The next option will be to maintain a separate table where the primary key of the deleted records are updated. We should load these values and remove all the rows in fact QVD corresponding to these primary key values. This will result in addition of a new script in our incremental load file to load the PK from deleted rec table and compare this with the QVD data and ignore these data.

Let me know if you need any clarification.

-Haneesh

Not applicable
Author

Hi Haneesh,

That's true! In both methods which you have suggested may not be efficient as it have to load the rows which will increase the document size and may have the performance impact due to this.

So my question is why we can't find out the deleted rows using the PKexp as anyway it uniquely identifies the rows??? Please let me know the same....

Not applicable
Author


Sunil Jain wrote:
suppose I have One Record Which have primary key as p1 . If I delete that record in your scenario and Add new record inmplace of that record what will be primary key ?. <div></div>


Oh ya! I think it will those primary keys will be replaced by this new one. So deleted records will not be available in the QVD. Correct me If I am wrong!

But my question is while concatenation we check whether the PK is already existing or not, if it doesn't exists then we used to concatenate the news rows. But old rows will be already there in the qvd right? Am I right? So what I mean to say is that it may contain deleted also!

Please let me know your suggestion for the same

suniljain
Master
Master

Rikab, You are right .

If Data deletion is in rare case then full extract that types of table in one time a year. It Increase consistency in that case.

Whatever we discuss till now regarding Rob's method it working fine and I am agree with you on above your understanding regarding increamental load.

Nice to discuss with you.

If you have still any query.

pls reply me.

Regards

Sunil Jain.

Not applicable
Author


Sunil Jain wrote:
But my question is while concatenation we check whether the PK is already existing or not, if it doesn't exists then we used to concatenate the news rows. But old rows will be already there in the qvd right? Am I right? So what I mean to say is that it may contain deleted also!<div></div>


I need clarity on above doubt. Please explain!

By the way I too had a nice discussion with you and so many other member from QlikCommunity.I still have few doubts will send you later some time.

QlikCommunity helps me alot as I am the only person in my organization who works in qlikview. So it provides me great assistance as it is the only way to get good knowledge when no one else is working along with me in real time.

Not applicable
Author

Hi Haneesh and Sunil,

I am not able to understand how the incremental Update(tab) script works. Will be much thankful to you if you can explain me in much better way.

Main problem I am little confused which is the left table and which is the right where we are using the right join. Requesting you to explain how the the complete script works.

Not applicable
Author

Hi Haneesh and Sunil,

I have explained the issue in the below screen shot please look in to it.

Attached the same document for you to work on!

Not applicable
Author


Rikab Kothari wrote:
Oh ya! I think it will those primary keys will be replaced by this new one. So deleted records will not be available in the QVD. Correct me If I am wrong!


Hi Rikab,

You are right. The PK for the deleted records will be replaced by the new records and so, in your case, the deleted records are also handled by the incremental load doc.


Rikab Kothari wrote:But my question is while concatenation we check whether the PK is already existing or not, if it doesn't exists then we used to concatenate the news rows. But old rows will be already there in the qvd right? Am I right? So what I mean to say is that it may contain deleted also!


If you look at the incremental Tab in the load script, it first loads the new data. Then it loads the data from QVD with 'WHERE NOT exists(PK, $(PKexp))' condition. So, here only the rows which do not have the PK values in the new records will be loaded and concatenated.

If the deleted records' PK is replaced by a new row in DB table then it will be changed in the QVD. But, if the deleted records' PK is not replaced in the DB table, then it will still reside in the QVD.

Let me knoe if you are still unclear.

-Haneesh