The Search String

[hic]

One of the strengths of QlikView is its search engine. With it, you can find pieces of information in a fraction of a second and select the found field values. But how is the search defined? And where can the search be used?

[hic]

Obviously, a search is defined by the search string that you enter when you search for something. But there are several different ways a search string can be interpreted. See for instance the picture below: In the normal search to the left you have a different result set from what you get in the wildcard search.

The logic is the following:

• Normal search
  The search string is matched against the beginning of every word in the field value. Normal search is used only in interactive searches.
• Wildcard search
  If the search string contains a wild card or the search string is used in a programmatic search (e.g. in a Set Analysis expression), a wildcard search is made instead of a normal search. This means a strict, case insensitive, match between the search string and the field values, where the only way of representing unknown characters is explicit use of wild cards.
• Numeric search
  If the search string begins with ‘<’ or ‘>’, a numeric comparison is made. E.g. ‘>=1000’. Only values that fulfill the numeric requirement will be matched.
• Expression search
  If the search string begins with an equals sign ‘=’, an expression search is made. E.g. ‘=Sum(Sales)>1000’. Then an aggregation is made for each value in the field, and a match is found if the expression is true. This means that you can make a selection in one field based on an aggregation in another field.
• Fuzzy search
  If the search string begins with ‘~’, a fuzzy search is made. This means that all field values are ranked according to similarity and the top one will be selected when you hit return.
• Compound search
  Using a compound search, you can express more complicated search conditions with logical operators. Use brackets and ‘&’ or ‘|’, e.g. ‘(California|Nevada)’.

Hence, how the search string is interpreted depends only on which characters it contains. The settings in the list box properties do not affect the evaluation. The "Default Search Mode" only affects how the initial search string is created.

Further, you can use the search string in a number of different places, not just in the search area in the user interface: You can also use it in Set analysis, in actions, in bookmarks, in API calls, etc.

In principle, you can combine any search string with any search place. There are however some anomalies and exceptions. For example, you cannot use normal search or fuzzy search in programmatic searches and you cannot use the advanced search modes in the Qlik Sense global search.

The bookmark deserves a special mention. If a search is made and the resulting selection is stored in a bookmark, the bookmark remembers the search string and not the selection. This means that if new values appear when the script is run, they may be selected by the bookmark, even though they didn’t exist when the bookmark was created.

HIC

Further reading related to this topic:

Text searches

Search - But what shall you find?

The Expression Search

[#3]

great!

[Anonymous]

Does it work also in Qlik Sense?

[rbecher]

Why Qlik (Qlikview and Qlik Sense) doesn't find anything behind a hyphen in a normal way?

You barely can find things In chained words (like German chained names as "Müller-Lüdenscheidt") behind the hyphen. This is very very poor..

[rory_mchugh]

Just stumbled on this, great post, very helpful 😄

[NZFei]

Sense search all the single words that start with what you input if you don't add wildcard.

For example, if you input 'L', it will find out all the single words started with "L".

If you input "-", it will find out all the single words started with "-". That is why you can't find any.

[er_abhichandra]

Hi Ralf, try *-L*

Regards,

Abhinava

[rbecher]

‌lol

Why it doesn’t treat this as single words is the problem here and everything else as searching for „L“ is no solution from a user perpective..

[dominicmander]

Hi hic‌,

If I search for "=1" (I'm thinking in particular in set analysis e.g. Sum({$<[SomeDualFlag]={"=1"}>}[Measure]) ) will this still perform a numeric search? Or do I need to do something like Sum({$<[SomeDualFlag]={">=1"}>} [Measure]) in order to get the performance benefit of a numeric search.

Sorry, think this might be a stupid question ... searching for "=1" will just return all values.

[hic]

If you search using an equals sign, then this must be followed by a Boolean expression. So, yes, searching for "=1" will just return all values.

If you search without an equals sign, e.g. Sum({$<SomeFlag={1}>} Measure), then the "1" will match the textual value of the dual flag, which in most cases will work just fine.

If you instead want to compare with the numeric value of the dual flag, you will need to make a numeric search, e.g. Sum({$<SomeFlag={"<=1"}>} Measure). To force it to accept just the 1 and not values below, you could write Sum({$<SomeFlag={"<=1>=1"}>} Measure).

HIC

[dominicmander]

Yes, I realised very quickly that that was what it was doing heh!

In my case the flag has a string representation e.g. Dual('Yes',1) so I came to the conclusion that my options were either a string comparison or a numeric search e.g. SomeFlag={">=1"}, and that a numeric comparison was not possible.

In the spirit of "Flag={1} is more performant than Flag={'1'}" I was trying to perform a numeric comparison.

A very quick crude test suggested SomeFlag={'Yes'} was faster than SomeFlag={">=1"}, though it's far from critical in my case. If it becomes worth it I might invest more time in confirming which approach is more performant in my case, but I think I can safely not worry about it for now.

Thanks for your reply

Dominic