QlikView - Rank Function

The Rank() function in QlikView 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 QlikView 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 QlikView.

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 Supplies,Presentation Supplies,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 Supplies,Display Board,29,2177

Load Script

The above data is loaded to the QlikView 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. Click OK and press Control+R to load the data into QlikView's memory.

rank_laod_script

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

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

rank_chart_type

Select the Chart Dimension

From the First Dimension drop down list, choose Product_Line as dimension. Click Next.

rank_select_diemnsions

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. Click Next.

rank_chart_expression

Displaying the Chart

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

rank_chart_data

Using Aggregate Function with Rank

The aggregate functions like − 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 line.

# Load the records with highest sales value for each product line.
LOAD Product_Line, 
     max(Value,1)
FROM
[E:\Qlikview\data\product_sales.csv]
(txt, codepage is 1252, embedded labels, delimiter 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. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below.

rank_aggr_func
Advertisements