Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The Search String

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?

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.

 

search h.png

 

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)’.

 

search num.png

 

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.

 

Type and place2.png

 

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

61 Comments
MVP
MVP

Thanks for this detailed version of Search !

0 Likes
1,003 Views
rajeshvaswani77
Valued Contributor III

Thanks Henric, this was really detailed.

0 Likes
1,003 Views
kuba_michalik
Contributor III

Thanks for the nice summary!

There is one question about QlikView search that has been bothering me and it seems like a good place to ask - what does the ^ do during a search? It's listed in the help as a wildcard meaning "whole string". But I can't for the life of me figure out what does it actually do Couldn't find any examples either.

1,003 Views
Not applicable

Bookmarked! Thanks Henric.

0 Likes
1,003 Views
Not applicable

I have the same problem as Jakub - can't find any information about ^ .

I see that this sign does something, but I can't figure out what...

0 Likes
1,003 Views
rohit18492
New Contributor III

Hiii guys...can any one explain me different techniques to optimize data

model??

0 Likes
1,003 Views
Employee
Employee

Jakub

You ask what the ^ does. I think I know what it should do, but I wonder what it is doing in the documentation. It exists in the documentation of QlikView 11 only. It exists neither in QlikView 10, nor in Qlik Sense. And it doesn't work the way it should. So I suspect it was a mistake that it was documented.

The background is that we have tested different ways of making a "precise match", i.e. a way to "override" the normal search and look for the entire field value. But the ^ wasn't a good idea and hasn't been pursued. So I would just ignore it.

HIC

Edit: It turns out that this answer is wrong. See 5 comments further down. 

1,003 Views
Luminary
Luminary

Excellent post! Thanks Henric!

JV

0 Likes
1,003 Views
Not applicable

very accessible explanation

0 Likes
1,003 Views
luciancotea
Valued Contributor

If we are on the subject, can't a small delay be added to the search box before starting to search?

It's becomes frustrating searching in big applications. Currently I write the string in Notepad, then paste it in the search box in order to avoid long waiting times.

0 Likes
1,003 Views
anderseriksson
Contributor III

Sounds like it should be like the regex start of string and end of string symbols but they forgot the end of string ($).

0 Likes
1,003 Views
Employee
Employee

Jakub

The above answer concerning the ^ turns out to be wrong. One of our developers enlightened me... And the documentation is wrong too. The symbol means beginning of word, and it should be combined with other wild cards.

Example

   *^h* will find field values that have a word beginning with 'h'. It is almost the same as

   * h* but the string with the ^ includes also the first word of the field value.

circumflex.png

HIC

1,003 Views
Not applicable

Henric, thank you for this summary. I have one more question about searching. Is there any possibility to find all strings containing "*" or "?" ?

0 Likes
1,003 Views
mov
Esteemed Contributor III

Henric,

A few weeks ago, massimo.grassi , troyansky and I had a discussion about cases-sensitivity in the set analysis search.  What we came up with, that it is case-sensitive if wildcards not used and the search string matches exactly one of the values.  If there is wildcard or the search string doesn't match any existing value, it is case-insensitive. 
Here is the discussion:
http://community.qlik.com/thread/152630
Can you comment on this?

Thanks,
Michael

0 Likes
1,003 Views
Luminary
Luminary

Hi Henric,

Thanks for the post. Yesterday I posted a tip on my blog (qlikcentral.com) around how I use two searches and conditional show to switch between Normal and Wildcard searches.

http://qlikcentral.com/2015/03/02/making-qlikview-searches-easy/

Regards

Richard

0 Likes
1,003 Views
Employee
Employee

B_ZDUNCZYK

No I don't think it is possible today. To do this we would need an escape sequence for special characters. Or a RegEx search.

HIC

0 Likes
1,003 Views
Employee
Employee

msolomov, maxgro, troyansky

Not sure I want to comment, more than saying that I consider this behaviour to be a bug. An annoying behaviour...

HIC

0 Likes
1,003 Views

That was my conclusion, too. Who volunteers to report the bug to the proper authorities?

Excellent blog, Henric! I enjoy every article and always look forward to the next!

0 Likes
1,003 Views

You can search for * or ? literal strings with the following Expression search. Assuming a fieldname of "X".

=index(X,'*')>0

-Rob

1,003 Views
Not applicable

Great tip, thank you Rob!

0 Likes
1,003 Views
Not applicable

Informative.

0 Likes
1,003 Views
sibideepak
Contributor II

Very useful post .. Thank you

0 Likes
1,003 Views
Not applicable

Thanks for the post.  Indeed I have a question about using the Expression Search and bookmark it.

If I added two bookmarks, 1. avg(reading1) > 1.05 or avg(reading1)<0.95

and 2. avg(reading2) > avg(reading2)+2*stdev(reading2)

As in bookmark help page, it said it will CLEAR all selections before applying the bookmark, unless you check on the "+" column in manage bookmark dialog.

However, it seems that the clear action is not done, i.e. I applied bookmark 1, and it selected 3 values.

Then I applied bookmark2, it will not select another 4 values that I expect, but instead just clear my selection unless I applied it again (of course).  Is it WAD?

0 Likes
1,003 Views
Employee
Employee

In an expression search the search string is evaluated given the current selection, so in your case it returns an empty result set, and no selection is made. Instead it clears the old selection.

If you instead try

   =Avg({1} reading1) > 1.05

you will search all values and probably get what you want.

The background is that an expression search is an aggregation, which means that a hypercube is created, in the same way as when you have a chart. This is different from a numeric search or a wildcard search where the symbol tables are used instead.

So, I'd say it's a WACD - Works As Correctly Designed.

HIC

1,003 Views
mwoolf
Honored Contributor II

Henric,

There appears to be a 1024 character limit to the search string. Do you know any way to increase that limit?

0 Likes
1,003 Views
Employee
Employee

Didn't know that that existed... I just tested it and, indeed there is such a limit on the input control of the list box in the desktop client. And no, there is no way to change this.

But there is no such limit on the search string itself. Longer search strings work fine in Set Analysis expressions and in Ajax list boxes.

HIC

0 Likes
1,003 Views
Not applicable

Henric,

Will be possible to implement levenshtein distance kind of thing with Qlik? I try it and i can achieve it with the power of Macro. Other than macro, is there any possibility? Levenshtein Distance is very similar to fuzzy search. Thats why i come up with such a question.

Thanks,

Omer

0 Likes
1,003 Views
2908nmadao
New Contributor III

Good morning Henric Cronström !

I have an issue with the search field or more of an enhancement that I would like to do to my application.

I have my tables qualified and when I perform a search, it shows the name of the table and does not look great. Is there a way to rename the fields when they appear in the search box?

Could you please let me know if this is possible?

Thanks in advance.

Kind regards,

Nelson.

0 Likes
1,003 Views
blaise
Contributor III

Hi Nelson

Sadly not possible, and this is one of my most requested / wanted feature, one fieldname for GUI/end-user and another fieldname for Datamodeling/Developer.

Qlik Sense helps a bit as you can declare meassures and dimension in your library - hope to this this in next version of QlikView.

0 Likes
1,003 Views
2908nmadao
New Contributor III

Thanks Johan Hessler!

This is a shame. I thought it would be possible to mask the field or maybe there is a workaround that someone found and hasn't shared yet?

Thank you very much!

Kind regards

Nelson

0 Likes
1,003 Views