Incremental loads create a much more efficient way of extracting and storing data in Qlik. If you have a large volume of data that does not change over time, it does not make sense to extract all of it every single time. Instead, why not just take the new data?
For an incremenatal load, first of all set up the first extract. The example below is a small example but this could be many hundreds of fields if needed.
This script connects to the data source and stores everything into a QVD called IncrementalQVD and then drops the table out of RAM.
With this set up we do not need this again, but dont get rid of it just in case you ever need to start the QVD again. Create a tab in script called Exit and put the code “exit script;” on the tab. Move the first load tab below this and you can always restore if needed.
After this we need to set up our incremental load. To do this we need to work out what is new and add it to the old data.
Step 1. Load in the QVD of all old data
Step 2. Load the Max of the field that signifies the newest data. In this example a date field.
Step 3. Use a variable to hold the maximum value. The code in the image below peeks inside the MaxDate table and stores the value into a variable called vMaxDate. I have then added a trace statement to pass this date into the load output details when the code is running so i can check it.
Step 4. Drop the MaxDate table. Once we have the variable, we dont need this anymore
Step 5. Load data from a data source and use the variable to say WHERE date is greater than the variable. This will only load data that is not in the QVD. At the top of the table there is a concatenate statement which forces the table to append on the bottom of the QVD table. With Qlik, if all columns are the same it will automatically concatenate the data into one table but its always best to make sure this will happen with a concatenate statement, especially if new columns are added and you have any Load * statements.
Step 6. Once this is all done, store all the data back over the top of the IncrementalQVD
Once this is all done, each time this runs it will take all new data, based on the max date, and add it to the existing QVD. This will take far less time than extracting everything each time and if you are loading from SQL it will put far less load on the system than a full extract.
