Overview of Qlik Incremental Loading

[Michael_Tarallo]

Hey Guys - I have been meaning to do a video on this topic for many years and I finally got around to it! Over the years as I taught myself Qlik, I would dabble with the idea of incremental loading - but never really had a grasp on it as my data sets were always used for demos or they were quite small.....until I started building my Qlik Sense Retro-Gaming Dashboard.

(I use the incremental loading best practice to insert new video game pricing data daily - which allows me to view the pricing history of my collection for "Loose" and "Complete in Box" values). As I was building my app I had a ton of resources available to me but I never really found a decent video on this topic that simply explained what it was or how it is used. I figured I would create something short and sweet with a simplistic example from what I have learned, with hope it helps you understand incremental loading a bit better than I did! There are a few prerequisites you should be aware of, such as understanding what .QVD files are and what there purpose is. As well as basic data model design that include key and date fields which are used to identify new and changed records. I've included some samples files and resources at the end of this post to help.

Let me know what you think and please leave your questions or comments below. Enjoy!

Incremental Load

Regards,

Michael Tarallo (@mtarallo) | Twitter‌

Qlik

Resources:

• Using QVD files for incremental load ‒ QlikView
• Incremental loading - All scenarios

Sample Insert Only - Incremental Load Script

//Simple Insert - New Records from Source Data

// Get the last OrderID loaded into the .QVD
MaxKeyLoad:
LOAD Max(OrderID) as MaxID
FROM [lib://IncLoadLocation (desktop-aura45o_mto)/sales_data.qvd] (qvd);

//Set a variable to be used in the SQL
Let MaxID = peek('MaxID',0,MaxKeyLoad);

//Get the latest data from the source where the OrderID in the Source is greater than the orderID in the .QVD
[orders]:
Load *;
SQL SELECT * FROM "Sales"."dbo"."Orders$"
WHERE OrderID > '$(MaxID)';

// Data is automatically concatenated if the data model has the sames fields
// CONCATENATE forces concatenation (in this example it is not necessary)
// Load all data from the .QVD
CONCATENATE
LOAD * from [lib://IncLoadLocation (desktop-aura45o_mto)/sales_data.qvd] (qvd);

//Store the new larger qvd file
// This will store the existing data and the new record and overwrite the existing .qvd
STORE orders into [lib://IncLoadLocation (desktop-aura45o_mto)/sales_data.qvd] (qvd);

// Exits script - used for debugging and troubleshooting - will not execute the next tab.
exit script;

Can't see the video? YouTube blocked in your region or office? - Download the .mp4 to watch on your computer or mobile device.

In case you want to perform this test with MS SQL - I added the SQL Queries and the MS SQL Database .bak file as well.

I also updated this to include a sample script - mikes_script.txt - this shows a simple insert using files as sources and a dropbox connection as the main connector for the inventory file.

sales_data.qvd

MSSQLQueries.zip

Sales.bak

Sales Analysis.qvf

Incremental Load.qvf

[Anil_Babu_Samineni]

My initial job making questionnaire. How this functionality works?

Let MaxID = peek('MaxID',0,MaxKeyLoad); 

[orders]: 

Load *; 

SQL SELECT * FROM "Sales"."dbo"."Orders$" 

WHERE OrderID > '$(MaxID)';

We are calling Variable into SQL script. How Qlik functionality used for SQL? Not sure, Is that really works?

[Lech_Miszkiewicz]

Hi Anil

this is very basic... i will break it into individual segments

• Let MaxID creates MaxID variable called MaxID
• peek('MaxID',0,MaxKeyLoad) is a Qlik function which should return:
  • Peek function searches MaxID field in row 0 in Table MaxKeyLoad and allocates found VALUE  to your MaxID variable (for explanation purpose lets assume that MaxId = 10045
• MaxID variable can be then passed as a value into SQL statement.

Above example assumes that OrderID is an increment and each new order get higher/bigger value

We are not aclling variable into SQL script - we are passing variable value into SQL where clause, which basicly means that instead $(MaxID) there is a VALUE passed through... so what gets passed to SQL is actually:

Load *; 

SQL SELECT * FROM "Sales"."dbo"."Orders$" 

WHERE OrderID > '10045';

will i pass your test Anil? 🙂

cheers

Lech