QpkView - Rank Function

The Rank() function in QpkView is used to display the rank of the values in a field as well as return rows with specific rank value. So it is used in two scenarios. First scenario is in QpkView charts to display the ranks of the values in the field and second is in Aggregate function to display only the rows, which have a specific rank value.

Input Data

The data used in the examples describing Rank function is given below. You can save this as a .csv file in a path in your system where it is accessible by QpkView.

Product_Id,Product_Line,Product_category,Quantity,Value
1,Sporting Goods,Outdoor Recreation,12,5642
2,Food, Beverages & Tobacco,38,2514
3,Apparel & Accessories,Clothing,54,2365
4,Apparel & Accessories,Costumes & Accessories,29,4487
5,Sporting Goods,Athletics,11,812
6,Health & Beauty,Personal Care,21,6912
7,Arts & Entertainment,Hobbies & Creative Arts,58,5201
8,Arts & Entertainment,Paintings,73,8451
9,Arts & Entertainment,Musical Instruments,41,1245
10,Hardware,Tool Accessories,2,456
11,Home & Garden,Bathroom Accessories,36,241
12,Food,Drinks,54,1247
13,Home & Garden,Lawn & Garden,29,5462
14,Office Supppes,Presentation Supppes,22,577
15,Hardware,Blocks,53,548
16,Baby & Toddler,Diapering,19,1247
17,Baby & Toddler,Toys,9,257
18,Home & Garden,Pipes,81,1241
19,Office Supppes,Display Board,29,2177

Load Script

The above data is loaded to the QpkView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. Cpck OK and press Control+R to load the data into QpkView s memory.

Creating Chart with Rank() Function

Next, we follow the steps given below to create a chart, which shows the rank of the filed Value described with respect to the dimension Product_Line.

Select the Chart Type

Cpck on the Chart wizard and choose the option straight table as the chart type. Cpck Next.

Select the Chart Dimension

From the First Dimension drop down pst, choose Product_Line as dimension. Cpck Next.

Define the Chart Expression

In the custom expression field, mention the rank expression as shown below. Here we are considering the numeric field named Value, which represents the Sales value for each category under each Product Line. Cpck Next.

Displaying the Chart

On cpcking Finish in the above step, the following chart appears which shows the rank of the sales value of each Product Line.

Using Aggregate Function with Rank

The aggregate functions pke − max, min etc. can take rank as an argument to return rows satisfying certain rank values. We consider the following expression to be out in the script editor, which will give the rows containing highest sales under each Product pne.

# Load the records with highest sales value for each product pne.
LOAD Product_Line, 
     max(Value,1)
FROM
[E:Qpkviewdataproduct_sales.csv]
(txt, codepage is 1252, embedded labels, depmiter is  , , msq)
group by Product_Line;

Creating Sheet Object

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