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: 
mastankomatla21
Contributor III
Contributor III

Incremental load by using primary key

Hi Team,

Good Morning!!

Could you please let us know,how can we implement incremental load without having Date field .If we can implement by using Primary key kindly show us the example script.

By using primary key can we implement following three methods in incremental load.

1.Insert

2.Insert and update

3.insert, update and delete

Regards,

Masthan

 

 

2 Solutions

Accepted Solutions
mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

Thanks a lot for your great explanation!!.Now this makes sense.

Regards,

Mastahn

 

View solution in original post

Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi Masthan

In theory you should be able to delete the records. That is what the Inner Join in Step 5 in my original post is for.

For example, say we have the following table in the QVD:

IDProduct
1Apple
2Banana
3Pear
4Grape
5Lemon

 

And then the source changes to the following:

IDProduct
1Apple
2Banana
3Pear
5Lemon
6Peach

 

When we go through the steps, then the new row loaded will be:

IDProduct
6Peach

 

When you concatenate the QVD it will become:

IDProduct
1Apple
2Banana
3Pear
4Grape
5Lemon
6Peach

 

When you Inner Join the IDs in the source file again, then ID 4 will be removed (since it is no longer in the source table's IDs) leaving you with:

IDProduct
1Apple
2Banana
3Pear
5Lemon
6Peach

 

This is then stored into the QVD again.

Hope this makes sense.

Regards,

Mauritz

View solution in original post

11 Replies
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi Masthan

I don't have example code, but perhaps I can share one or two high level ideas. Firstly I don't think you can have update since there is nothing which can be used to see if an update has been made to the data. Is this assumption correct?

  1. Initial load loads all of the data (let's call the primary key column ID) from your source file. Order By ID Desc. The reason for this is because the biggest ID will always be on top for the next step. Store this into the QVD.
  2. Next time you load, load the Top 1 ID from your QVD into a temp table and use Peek to find it's value (let's call this variable vTopID). Drop the temp table.
  3. Next, load all records from the source file (not QVD) which have an ID > $(vTopID) into memory (once again in desc ID order).
  4. Load/concatenate the records in the QVD to the table in memory (with the condition WHERE NOT Exists(ID)).
  5. Inner join all of the IDs from the source file to your table in memory (to account for deleted records).
  6. Store the table in memory into the QVD (overwriting the old one).
  7. Drop table in memory.
  8. Repeat steps 2-7 every time.

You can play around with using RecNo()=1 instead of Top 1 to see if it has any performance improvements. From this thread is seems like it doesn't.

Hope this helps.

Regards,

Mauritz

 

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

Appreciate for your response on this.

From your end:  Firstly I don't think you can have update since there is nothing which can be used to see if an update has been made to the data. Is this assumption correct?

As per my understanding-can we consider highest Primary key ID as a updated value?I believe we can implement update method as well.can we remove duplicates as well here? Kindly confirm on this. Thanks in advance!!!

Regards,

Masthan

 

 

Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi Masthan

Sorry, I forgot to mention the WHERE NOT Exists(ID) part in my original response (now marked in red) which will take care of the duplicates. However, this is only when a value which is already in the QVD has been updated which is not applicable to you. In my explanation 'update' refers to a field which exists, but which has now changed. Inserts or new values will be handled by my method as well as deletes, but if you are using ID to identify new records then there is no way of seeing whether an existing record has been modified.

For example, let's say you have this data set when you load for the first time:

IDProduct
1Apple
2Banana
3Pear

 

These items will be stored into the QVD. The next time you load, you will only load the items with an ID greater than 3 and append the original items to your table. So if your data set now looks like this:

IDProduct
1Apple
2Kiwi
3Pear
4Grape
5Lemon

 

you will only load the items in blue resulting in the following table:

IDProduct
1Apple
2Banana
3Pear
4Grape
5Lemon

 

There is no way that you can see that ID 2 changed from Banana to Kiwi. Hence you cannot update existing items.

Hope this makes sense.

Regards,

Mauritz

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

Thanks a lot for your great explanation!!.Now this makes sense.

Regards,

Mastahn

 

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

As i analyzed, we will not be able to implement delete method as well.Kindly find my below analysis. Please correct me if i am wrong. Kindly find the attachment for reference.

Issue is-Despite I have deleted 125 in source Still I am getting in 125 record in my out put(highlighted)
 
Product IDItemSalesStore Name   
121Corn Bread245JA   
123Potato Salad260MM   
125Half Chicken300PTDeleted record in below source  
127Potato Salad260NS   
129Becon Fries200NCInserted  
131Corn Bread245MMInserted  
 Inner Join     
121Corn Bread245JA   
123Potato Salad260MMoutput of Delete method  
127Potato Salad260NS   
129Becon Fries200NC   
131Corn Bread245MM   
       
Issue is-Despite I have deleted 125 in source Still I am getting in 125 record in my out put(highlighted)

Regards,

Masthan

 
 
 
 
 
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi Masthan

In theory you should be able to delete the records. That is what the Inner Join in Step 5 in my original post is for.

For example, say we have the following table in the QVD:

IDProduct
1Apple
2Banana
3Pear
4Grape
5Lemon

 

And then the source changes to the following:

IDProduct
1Apple
2Banana
3Pear
5Lemon
6Peach

 

When we go through the steps, then the new row loaded will be:

IDProduct
6Peach

 

When you concatenate the QVD it will become:

IDProduct
1Apple
2Banana
3Pear
4Grape
5Lemon
6Peach

 

When you Inner Join the IDs in the source file again, then ID 4 will be removed (since it is no longer in the source table's IDs) leaving you with:

IDProduct
1Apple
2Banana
3Pear
5Lemon
6Peach

 

This is then stored into the QVD again.

Hope this makes sense.

Regards,

Mauritz

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

Its great to see ,working as i expected. Many thanks!!!

 

Regards,

Masthan

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

Kindly share me the few challenges along with proper solutions which you have faced in QlikSense so far.

Kindly share me the few value added servicess(ex: how can you manage the client with the help of different types strategies which you have implemented).

Thanks in advance!!!!

Regards,

Masthan

 

 

mastankomatla21
Contributor III
Contributor III
Author

Hi Mauritz,

In delete method i have noticed below discrepancy. 

1. while doing the inner join, if i use Id column only from source, the deleted record also getting into Target qvd.(In correct)

2.but while doing the inner join, if i use all columns from source(Id,Product) , the deleted record not getting.(This is correct only).

May i know why i am getting this variance(1st step).

Regards,

Masthan