QlikView - Match Function


Advertisements

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

Matched_func_script

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.

Matched_func_data

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

wildmatch_script

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.

wildmatch_data
Advertisements