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