Incremental load by using primary key

[mastankomatla21]

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

[Mauritz_SA]

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]

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]

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:

ID	Product
1	Apple
2	Banana
3	Pear

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:

ID	Product
1	Apple
2	Kiwi
3	Pear
4	Grape
5	Lemon

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

ID	Product
1	Apple
2	Banana
3	Pear
4	Grape
5	Lemon

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 · Accepted solution]

Hi Mauritz,

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

Regards,

Mastahn

[mastankomatla21]

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 ID	Item	Sales	Store Name
121	Corn Bread	245	JA
123	Potato Salad	260	MM
125	Half Chicken	300	PT	Deleted record in below source
127	Potato Salad	260	NS
129	Becon Fries	200	NC	Inserted
131	Corn Bread	245	MM	Inserted
	Inner Join
121	Corn Bread	245	JA
123	Potato Salad	260	MM	output of Delete method
127	Potato Salad	260	NS
129	Becon Fries	200	NC
131	Corn Bread	245	MM
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 · Accepted solution]

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:

ID	Product
1	Apple
2	Banana
3	Pear
4	Grape
5	Lemon

And then the source changes to the following:

ID	Product
1	Apple
2	Banana
3	Pear
5	Lemon
6	Peach

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

ID	Product
6	Peach

When you concatenate the QVD it will become:

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

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:

ID	Product
1	Apple
2	Banana
3	Pear
5	Lemon
6	Peach

This is then stored into the QVD again.

Hope this makes sense.

Regards,

Mauritz

[mastankomatla21]

Hi Mauritz,

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

Regards,

Masthan

[mastankomatla21]

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]

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