Easy Text Search

People like the feature in QlikView that lets you start typing a word or phrase on top of a listbox (or the pull-down for a particular field on a multibox) and QlikView automatically shows you a list of the fields that contain that word or phrase. Notice that when you begin typing, a small box appears that contains the word you typed in between two asterisks. For example, if you click on the listbox for product names and start typing the word “cheese” a box appears containing *cheese*
The feature is called a text search and the asterisks are wildcard characters. The asterisk represents any string of characters… so the list of values that QlikView is showing you in our example is any group of characters, followed by “cheese”, and followed again by any group of characters… which really means any value containing the letters “cheese”. If you wanted only product names that begin with the word cheese then remove that first asterisk (so that the box contains cheese* ) and you will see only product names beginning with the word “cheese”. Similarly, remove the second asterisk and you only see product names that end with the word “cheese”.

Another wildcard character you can use is a question mark, “?”. The question mark represents any single character. I use it all the time when the field is a date field in the form of mm/dd/yyyy… then , for example, I can type something like 10/??/2010 and it will show me a list of all of the date values in October 2010 (“10/01/2010”,”10/02/2010”, etc.)

(Author's note: Nov 4, 2010. With QlikView version 10 the default behavior of the listbox is not to automatically insert wildcard asterisks as you start to type text. But, the search behavior is very similar and you can still type your own wildcard characters as needed.)

★ ★ ★

Selecting Null Values

It’s true that you cannot select the null values of a field. But, you can usually do something that will work even better to identify the null values. The idea is that you select values of a different field that are associated with the null values you’re interested in. As always, to help communicate the idea, here’s an example:

Imagine that you have a report of sales data but there’s a defect in the data and some of the products don’t have a product description. You can’t make a selection on missing or null values in the product description field, named PROD_DESC for our example, but you can easily select the products that have a missing or null product description. Go to the listbox for the PRODUCT_ID field or click the pull-down arrow for PRODUCT_ID from a multibox. Now, start typing this expression:

=isnull(PROD_DESC)

click Enter to finish the selection and you now have a selection consisting only of products that are missing their descriptions.

You can use a slightly different expression to select products with a blank or null product description; type this instead =isnull(trim(PROD_DESC))

This technique is useful if you use QlikView to find data quality issues or to validate data from suppliers or customers.

★ ★ ★