The Match() function in QlikView is used to match the value of a string on expression with the data value present in a column. It is similar to the function that we see in the SQL language. It is useful to fetch rows containing specific strings and it also has an extension in form of wild match() function.
Let us consider the following data as input files 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 wild match() function is an extension of the 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 wild match 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.
