The Match() function in QlikView is used to match the value of a string on expression with data value present in a column. It is similar to the in function that we see in SQL language. It is useful to fetch rows containing specific strings and it also has an extension in form of wildmatch() function.

Let us consider the following data as input file for the examples illustrated below.

Product_Id,Product_Line,Product_category,Product_Subcategory

1,Sporting Goods,Outdoor Recreation,Winter Sports & Activities

2,Food, Beverages & Tobacco,Food Items,Fruits & Vegetables

3,Apparel & Accessories,Clothing,Uniforms

4,Sporting Goods,Athletics,Rugby

5,Health & Beauty,Personal Care

6,Arts & Entertainment,Hobbies & Creative Arts,Musical Instruments

7,Arts & Entertainment,Hobbies & Creative Arts,Orchestra Accessories

8,Arts & Entertainment,Hobbies & Creative Arts,Crafting Materials

9,Hardware,Tool Accessories,Power Tool Batteries

10,Home & Garden,Bathroom Accessories,Bath Caddies

11,Food, Beverages & Tobacco,Food Items,Frozen Vegetables

12,Home & Garden,Lawn & Garden,Power Equipment

13,Office Supplies,Presentation Supplies,Display

14,Hardware,Tool Accessories,Jigs

15,Baby & Toddler,Diapering,Baby Wipes

Load Script with Match() Function

The following script shows the Load script, which reads the file named product_categories.csv. We search the field Product_Line for values matching with strings ‘Food’ and ‘Sporting Goods’.

Creating Sheet Object

Let us create a Table Box sheet object to show the data generated by the match 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 then select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below.

Load Script with Wildmatch() Function

The wildmatch() function is an extension of match() function in which we can use wildcards as part of the strings used to match the values with values in the fields being searched for. We search for the strings ‘Off*’,’*ome*.

Creating Sheet Object

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

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.

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.

So, this brings us to the end of blog. This Tecklearn ‘Using Match and Rank Function in QlikView’ blog helps you with commonly asked questions if you are looking out for a job in QlikView BI. If you wish to learn QlikView and build a career in Business Intelligence domain, then check out our interactive, QlikView Certification Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/qlik-view-certification-training/

QlikView Certification Training

About the Course

Tecklearn’s QlikView Certification Training will help you become an expert in Data Visualization with QlikView. Learn all the basics and advanced features of QlikView such as data modelling, reports and dashboards, visualization, object formatting, system table etc. Along with this, you will be given hands-on working experience on real-time projects that will help you pass the QlikView certification exam. You also get hands-on experience in QlikView applications.

Why Should you take QlikView Training?

The average annual pay for a QlikView Professional is $122,000. -PayScale.com.
Deloitte, Cisco, Qualcomm, Sony, AON & other top Fortune 500 companies use QlikView
QlikView – A Leader in 2017 Gartner Magic Quadrant for Business Intelligence & Analytics Platforms (For 7th Consecutive Year).

What you will Learn in this Course?

Introduction to QlikView

Concept of Business Intelligence
Features and components of QlikView
Comparison with other BI tools
Architecture
Installation & Navigation
QVS, QVW and .log files
Sheet Objects
Dimensions and Expressions
Various file types and extensions

Various QlikView Products

Overview of the various QlikView products

Introduction to Data and Scripting

Structuring the Script
Create tabs in the scripts, Debugging of scripts
Hands On

Data Model

QlikView Data File Types: QVD, QVX
System fields
Star schema
Synthetic Key Tables
Data Modelling Considerations
Straight and Pivot Table
Hands On

Components of Qlik View

Sheets and Sheet Objects
The List Box
The Table Box
The Multi Box
The Button & Text Object
Basic Charts
Hands On

Set Analysis

Set analysis
Working with modifiers, identifiers, operators, example of expressions,
Indirect set analysis
YTD & MTD

QlikView Functions, Reports and Charts

Uses of Expressions
Chart Properties: Dimensions
Qlikview Functions
Adhoc Reports
Incremental Load using QVD Files
Bookmarks
Hands On

Data Analysis

What-if Analysis
Comparative Analysis
Dynamic Reporting or Ad-hoc Reporting
Document Analyzer

QlikView Security

QlikView security
Defining the access controls and levels of access
Maintaining the access control database

QlikView Administration

QMC (QlikView Management Console)
Server Installation & Deployment
QlikView Project

Got a question for us? Please mention it in the comments section and we will get back to you.

670