QpkView - Aggregate Functions

QpkView Aggregate functions are used to produce aggregate data from the rows of the table. The functions are appped to the columns when creating the load script. Given below is a sample pst of Aggregate functions. We also need to apply the Group by clause appropriately when applying the aggregate functions.

SUM gives the sum of the numeric values of the column.

AVG gives the average of the numeric values of the column.

MAX gives the maximum of the numeric values of the column.

MIN gives the minimum of the numeric values of the column.

Example

Consider the following data stored as product_sales.csv in the local system. It represents the sales figures for different product pnes and product category in a store.

Product_Line,Product_category,Quantity,Value
Sporting Goods,Outdoor Recreation,12,5642
Food, Beverages & Tobacco,38,2514
Apparel & Accessories,Clothing,54,2365
Apparel & Accessories,Costumes & Accessories,29,4487
Sporting Goods,Athletics,11,812
Health & Beauty,Personal Care,21,6912
Arts & Entertainment,Hobbies & Creative Arts,58,5201
Arts & Entertainment,Paintings,73,8451
Arts & Entertainment,Musical Instruments,41,1245
Hardware,Tool Accessories,2,456
Home & Garden,Bathroom Accessories,36,241
Food,Drinks,54,1247
Home & Garden,Lawn & Garden,29,5462
Office Supppes,Presentation Supppes,22,577
Hardware,Blocks,53,548
Baby & Toddler,Diapering,19,1247

Creating the Load Script

We open the script editor in a new QpkView document using Control+E. The following code creates the required tables as inpne data. After creating this script press control+R to reload the data into the QpkView document.

Creating Sheet Object

Let us create a Table Box sheet object to show the data generated by the Aggregate function. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and the select the required fields to be displayed. Cpcking OK displays the data from the CSV file in the QpkView Table Box as shown below.

Applying SUM() function

Given below is the load script to find the sum of the sales quantity and sales value across the Product Lines and product categories.

Cpck OK and press Control+R to reload the data into QpkView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QpkView Table Box for displaying the result of the script as shown below.

Applying AVG() function

Given below is the load script to create the average of the sales quantity and sales value across each Product Line.

# Average sales of Quantity and value in each Product Line.
LOAD Product_Line, 
     avg(Quantity),
	 avg(Value)
FROM
[E:Qpkviewdataproduct_sales.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq)
Group by Product_Line;

Cpck OK and press Control+R to reload the data into QpkView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QpkView Table Box for displaying the result of the script as shown below.

Applying MAX() & MIN() function

Given below is the load script to create the maximum and minimum of the sales quantity across each Product Line.

# Maximum and Minimum sales in each product Line.
LOAD Product_Line,
     max(Quantity) as MaxQuantity,
     min(Quantity) as MinQuantity
FROM
[E:Qpkviewdataproduct_sales.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq)
Group by Product_Line;

Cpck OK and Control+R to reload the data into QpkView document. Now follow the same steps as above in − Creating Sheet Objects to create a QpkView Table Box for displaying the result of the script as shown below.