QlikView Search – 8 Types of Search Options in QlikView
In our last chapter, we talked about QlikView Security. Today, we will discuss a new chapter Qlikview Search. Along with this, we will learn different types of Searches in QlikView: Normal Search, Wildcard Search, Fuzzy Search, Numeric Search, Expression Search, Compound Search, Associative Search, and Advanced Search in QlikView.
Searching for specific fields or field values in data tables having tens of thousands of data values is for sure a tedious and daunting task. Thus, QlikView search comes in very handy for users dealing with huge amounts of data.
So, let’s start QlikView Search Tutorial.
2. QlikView Search
QlikView has a very efficient search engine. Although, one way of finding required data values is through selecting desired values manually in the table. For instance, if in a sales document, you only want sales information of USA, then you will have to click on the data field where the USA is written. This will take up the USA as the selected value and will display only the values related to and associated with the USA, deactivating all the other irrelevant values. A separate search object is available in QlikView through which you can filter through the vast ocean of information and get information so that you get to deal with only the values of your interest. Searching for values using the Search object is a lot faster than making selections manually.
The mode of searching through a search object is via writing a text search or string. Also, the search string can be typed in any case (upper or lower or mixed) and is not case sensitive. The search can use either by creating a separate search sheet object. To create a Search Object, either go to add new sheet object from the Layout tab or right-click anywhere on the sheet and select New Sheet Object>Search Object…. A search object will appear where you can type your search string. In the settings window, you can select what data fields and tables you want to be available and search data from.
Another way to invoke searching in QlikView is through the List Box sheet object. Create a list box by going to Layout>New Sheet Object>List Box. Select the data field from the loaded table whose values you want to be displayed in the List box. Once you are done with creating the list box as desired and it gets displayed, you will have a search bar on the top of the list box.
You can search for relevant and present values through that search bar. If after searching for desired values, you make a selection in the list box, all the other active sheet objects (table box, charts etc.) will filter their information leaving only the relevant data values on display (associative property). The search can also ripple through multiple active list boxes present on the same sheet and in that case all these list boxes will respond to the search string typed in one list box amongst them.
Search in a list box.
3. Types of Search in QlikView
Data can search in several different ways depending upon the purpose of search and type of data values being searched. The difference between types of search originates from the way a string is interpreted. Let us explore the seven types of QlikView search.
i. QlikView Normal Search
A Normal Text Search in QlikView takes up simple (no wildcards) text strings and looks for its match in the field values by searching just the beginning of every word in the values. The first image shows how a normal search responds.
The second image shows that if there is more than one string entered in the search bar and each string is separated by an empty space. Then each string will evaluate separately and return results accordingly. The empty spaces interpreted as the logical operator OR.
ii. QlikView Wildcard Search
The wildcard search in QlikView is an extended version of the normal search and is also a type of text search. The only difference between a normal and wildcard search is the way their strings are evaluated or interpreted. The wildcard search includes the use of several wildcards like:
This wildcard is used with letters or characters to find a perfect match in the field values. There are three ways in which this wildcard can be used.
First is before a letter *a this will give all the values that have words starting with the letter A or a.
A second way is after a letter, a* which returns all the values ending with the letter A or a.
And the third way is before and after a letter, *a* which returns the values with words containing the letter a.
Also, the use of space must be done attentively while searching using wildcards. Because let’s say you search ‘*paper’ , it may return the values ending with ‘paper’ like Newspaper and just the word Paper as well (if it resides in the data field). But if you put a space and make it * paper then the search will be only those values that end with ‘paper’.
This wildcard is used when the user is not sure about the spelling of the word he is looking for. In place of the letters the user is unsure about, ‘?’ a wildcard is used. For instance, if you search b?ck, it will return all the values like a buck, back, beck, bick etc. Also, the search string can be less precise like H?? T???e and the search will return the values with three letter words starting with H and a five-letter word staring with T and ending on e.
Much like in the normal search, this wildcard searches the words starting with the searched letters using ^ wildcard. Like, ^Ra will return the values having words starting with Ra. This is different from the normal search as it is used in programming search like in a Set Analysis expression
There are certain interesting facts about the wildcard search. It is a strict, precise and case-sensitive match where the value entered in the string search is matched accurately with the field value. Unlike the normal search, space is not considered as a logical OR in a wildcard search. A string having wildcards will only return values that accurately match the string hence no space or alternative search results emerge. For example, if you search ‘*newspaper’, only the value ending with the word newspaper will be displayed and not the values containing the word newspaper like ‘This newspaper is yesterday’s’ as it does not end with newspaper.
iii. QlikView Fuzzy Search
Fuzzy search is used in the case of a misspelling of a word or when the user is unsure about the way a value is written exactly. When you make a fuzzy search using a tilde-character (~) before the search string, the result will be arranged according to the degree of resemblance of the field values to the search string you have typed. The search result list goes from most resembling values to the least resembling values. If after getting the search results you press Enter, then the search automatically selects the first value from the list.
iv. Qlikview Numeric Search
The numeric search is a numeric variant of a text search. The difference is that it is used for searching field values containing numbers. The numeric search must start with one of the four relational operators,
a. > (Greater than)
For example, >10,000 will give all the values that are greater than 10,000.
b. >= (Greater than or equal to)
For example, >=10,000 will give the values greater than 10,000 but will also give 10,000 if present in the dataset.
c. < (Less than)
For example, <10,000 will return all the values less than 10,000.
d. <= (Less than or equal to)
For example, <=10,000 will return the values less than 10,000 as well as 10,000 (if present).
v. Qlikview Expression Search
The expression search, as the name suggests, takes up expression strings as input, evaluated that expression and returns search results matching the evaluation results. Every expression search begins with an equal to sign ‘=’. To search by an expression, it is recommended to type the string in aggregate function expression type. This ensures proper results. In the images given below, the expression search is used to find certain values greater than or less than the mentioned values from the sum happening on field values individually.
vi. QlikView Compound Search
The compound search is used to carry out logically complex searches. The search expressions use several wildcards and logical operators like ‘&’ and ‘|’. The complex expressions used in compound search can be stored in Actions or Bookmarks.
vii. QlikView Associative Search
The associate search is a search mode in the list box. It is an indirect search where the search string entered for the field values present in the list box also shows the match results with the values of other fields. Let us understand with the example of the image given below.
Here the list box is made for the field ‘Product_name’. When we type a search string ‘b’ all the values in the field Product_name having words starting with the letter B comes as a result. Now to see the associative search results click on the chevron ‘>>’ given at the right of the search bar. You will find a second list of search results which are the values matching the search string criterion but are from another field of the table called ‘Product_Type’. This type of indirect searching is called associative search.
viii. QlikView Advanced Search
The advanced search is used in case of searching through complex strings. The advanced search is invoked by pressing Ctrl+Shift+F. Once the search dialogue box opens you can enter advanced and complex queries in there. If you start the search string with ‘=’ then you will be able to enter advanced search expressions. The expressions follow the logic of Boolean functions where if the values contained in a field matches the search criterion the expression returns True for matched values and False for the unmatched. The matched values are then displayed as the search result.
An example of an advanced search is,
=StoreLocation like ‘I*’ or StoreLocation like ‘*A’
This search will return all the values having words that either start from the letter I or end with the letter A, contained in the field StoreLocation.
The advanced search dialogue box is different from the normal text search box. The search expression entered in advanced search only executes after the user clicks on the Go button.
There are several other options available to the user in the advanced search box.
a. Search in
The Search in bar has all the active fields available which can also changes by the user from the drop-down list. The search expression will be applied only on the values of the field selected here.
b. Search Expression
This is the space where the search string or expression is typed.
By clicking the Go button, the search string entered is applied or implemented on the field values of the field selected and search results are displayed.
With the Back option, you can go to the last selection that you made. The last 100 selections made by you are stored in QlikView’s memory.
It goes to the selection next to the current selection made. It is of use when to went back several selections using the Back button. To come back to the previous selections, the Forward button is used.
f. Clear Field
It clears the current field selection.
g. Clear All
This clears all the active selections of the document.
It opens the advanced search Help dialog box.
Closes the dialog completely.
The tabs present at the bottom half window of the advanced search dialog box, you can select and paste syntax of QlikView Field expressions, Functions and Variables. You can edit the copied syntax as you please in the Search Expression window.
So, this was all about QlikView Search. Hope you like our explanation.
This was a complete account of the QlikView Search option are available. QlikView Search is a very useful tool with great flexibility in terms of ways in which the user can carry out the search task. We hope this tutorial provided a comprehensive account of the topic with helpful examples. Share your feedback with us!
Related topic – QlikView Dashboard