QpkView - Incremental Load

As the volume of data in the data source of a QpkView 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 QpkView 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 QpkView 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 QpkView. 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 QpkView

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 QpkView document as a .qvw file.

Verifying the Data Loaded.

We can check the data loaded to QpkView 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 cpck 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 Supppes,Presentation Supppes,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:Qpkviewdataproducts.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:Qpkviewdataproduct_categories.csv]
	 (txt, codepage is 1252, embedded labels, depmiter 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:Qpkviewdataproducts.qvd](qvd);

//Store the values in qvd.
store NewProducts into [E:Qpkviewdataproducts.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.