QlikView - Incremental Load
As the volume of data in the data source of a QlikView document increases, the time taken to load the file also increases which slows down the process of analysis. One approach to minimize this time taken to load data is to load only the records that are new in the source or the updated ones. This concept of loading only the new or changed records from the source into the QlikView document is called Incremental Load.
To identify the new records from source, we use either a sequential unique key or a date time stamp for each row. These values of unique key or data time field has to flow from the source file to QlikView document.
Let us consider the following source file containing product details in a retail store. Save this as a .csv file in the local system where it is accessible by QlikView. Over a period of time some more products are added and the description of some product changes.
Product_Id,Product_Line,Product_category,Product_Subcategory 1,Sporting Goods,Outdoor Recreation,Winter Sports & Activities 2,"Food, Beverages & Tobacco",Food Items,Fruits & Vegetables 3,Apparel & Accessories,Clothing,Uniforms 4,Sporting Goods,Athletics,Rugby 5,Health & Beauty,Personal Care 6,Arts & Entertainment,Hobbies & Creative Arts,Musical Instruments 7,Arts & Entertainment,Hobbies & Creative Arts,Orchestra Accessories 8,Arts & Entertainment,Hobbies & Creative Arts,Crafting Materials 9,Hardware,Tool Accessories,Power Tool Batteries 10,Home & Garden,Bathroom Accessories,Bath Caddies 11,"Food, Beverages & Tobacco",Food Items,Frozen Vegetables 12,Home & Garden,Lawn & Garden,Power Equipment
Loading the Data into QlikView
We will load the above CSV file using the script editor (Control+E) by choosing the Table Files option as shown below. Here we also save the data into a QVD file in the local system. Save the QlikView document as a .qvw file.
Verifying the Data Loaded.
We can check the data loaded to QlikView document by creating a sheet object called Table Box. This is available in the Layout menu and New Sheet Objects sub-menu.
Creating the Table Layout
On selecting the Table Box sheet object, we get to the next screen, which is used to select the columns and their positions in the table to be created. We choose the following columns and their positions and click Finish.
Viewing the Existing Data
The following chart showing the data as laid out in the previous step appears.
Updating the Source Data
Let us add the following three more records to the source data. Here, the Product IDs are the unique numbers, which represent new records.
13,Office Supplies,Presentation Supplies,Display 14,Hardware,Tool Accessories,Jigs 15,Baby & Toddler,Diapering,Baby Wipes
Incremental load script
Now, we write the script to pull only the new records form the source.
// Load the data from the stored qvd. Stored_Products: LOAD Product_Id, Product_Line, Product_category, Product_Subcategory FROM [E:\Qlikview\data\products.qvd] (qvd); //Select the maximum value of Product ID. Max_Product_ID: Load max(Product_Id) as MaxId resident Stored_Products; //Store the Maximum value of product Id in a variable. Let MaxId = peek('MaxId',-1); drop table Stored_Products; //Pull the rows that are new. NewProducts: LOAD Product_Id,Product_Line, Product_category,Product_Subcategory from [E:\Qlikview\data\product_categories.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq) where Product_Id > $(MaxId); //Concatenate the new values with existing qvd. Concatenate LOAD Product_Id,Product_Line, Product_category, Product_Subcategory FROM [E:\Qlikview\data\products.qvd](qvd); //Store the values in qvd. store NewProducts into [E:\Qlikview\data\products.qvd](qvd);
The above script fetches only the new records, which are loaded and stored into the qvd file. As we see the records with the new Product IDs 13, 14 and 15.