Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.