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 values. So it is used in two scenarios. The first scenario is in QlikView charts to display the ranks of the values in the field and the 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 the 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.
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.
Select the Chart Dimension
From the First Dimension drop-down list, choose Product_Line as a dimension. Click 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. Click Next.
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.
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 the 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.
Thanks a lot for the post. Will read on…