Qlik Community

Qlik Design Blog

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

Text searches

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. The response is immediate, which is necessary for the user experience. Without it, you would easily get an empty result set without understanding why.

 

Search strings can be made in many different ways, and QlikView will respond differently depending on how the search string is defined. Normally you just enter a text, and QlikView will match this against the beginning of the words in the field values. If several strings are entered, QlikView will return the union of the matches of each of the strings.

 

search multi.png

 

But if you instead use a wildcard in your search string, the evaluation of the search string will be made in a different way: the entire search string with the wild card will be matched against the entire field value, sometimes yielding more matches, sometimes fewer.

 

search h.png

 

If you want to create more complex search strings (and e.g. store them in actions or bookmarks) you can do this too. Just use (, |, & and double quotes to define the syntax.

 

search compound.png

 

In all the above cases, the search and the selection are made in one and the same field. But sometimes you want to make the selection in one field, but make the search in another. This can be done using the associated search, which is an indirect search method. Start with the field where you want to make the selection, enter the search string, and click on the small chevron to the right. You will then get a list of other fields containing this search string. By clicking the desired match, you will narrow down the number of matches in the primary list to show just the relevant values. You can then make your selection by hitting Enter.

 

search indir.png

 

Further, did you know that

  • In the user preferences and in the list box properties, you can define how a default search string should be created, but this does not affect how it is evaluated – only how it is created. Once created, you can add or remove wild cards as you please.
  • When you make a search and save the resulting selection in a bookmark, the bookmark will contain the search string and not the list of selected values. When the bookmark is applied, it will perform the search and select the found values. If data has changed, this may imply a different search result than before.
  • You can use the same search string in many places: In list boxes, in Set analysis, in the Advanced search dialog, in actions and in bookmarks.

 

Bottom line: The search string is a powerful tool that helps you find the values you want. Use it.

 

HIC

 

Further reading related to this topic:

Search - But what shall you find?

The Search String

The Expression Search

33 Comments
carbal1952
Contributor II

Thanks Henric, Very useful, as always. Regards.

0 Likes
1,129 Views
rustyfishbones
Honored Contributor II

Explained and illustrated exceptionally well

Thanks Henric

0 Likes
1,129 Views
Not applicable

I wish for a way to turn off associated search for a listbox. I can set "List of fields" to empty and it don't execute, but the search expand and 'indicating' a associated search.

0 Likes
1,129 Views
bgarland
Contributor III

Thanks Henric. I'd love to see a Part 2 that covers the use of expressions in searches, and how those can be saved as dynamic bookmarks.

1,129 Views

Sounds like a plan.

0 Likes
1,129 Views
Partner
Partner

Thanks. Perfect explanation

0 Likes
1,129 Views
Partner
Partner

Thanks Henric. If you use *new *sh* wouldn't you get also the same result?

EDIT: Tak

0 Likes
1,129 Views

Your string will also find 'New Hampshire' but they are still not really the same. Your search string assumes a specific order of the two sub-strings, whereas the compound search does not. The following search will not return the same result if you write it your way:

search o nn.png

HIC

1,129 Views
Employee
Employee
0 Likes
1,129 Views
Partner
Partner

Thanks Henric for beautiful explanation. I like it.

0 Likes
1,129 Views
Partner
Partner

hi HIC,

interesting blog

0 Likes
1,129 Views
Not applicable

Henric, thank you for briging this much-ignored functionality to our attention. You mention the use of Bookmarks and I was wondering if you could help me with my query regarding Bookmarks with Dynamic Target Line on a Bar Chart: http://community.qlik.com/thread/96235


0 Likes
1,129 Views
philbishop
Contributor

I can't believe after all these years that you are still teaching me new things! Excellent description. You mention it at the end, but I would love to see a future article explain the tight relationship between Set Analysis and Search. Many times I will use a listbox search to test my Set Analysis expression - especially for numeric search and dates.

1,129 Views
MVP
MVP

Henric,

I appreciate another of your famous blog posts, nice and clear.

Regarding

(*o* & *nn*)

the ampersand character used here is more a logical operator than a string concatenation operator, right?

Can we find any comprehensive information about the search syntax somewhere?

Honestly, I think the search feature is not really well documented.

Is there any kind of syntax description like the one attached to the set analysis HELP page (BNF style)?

So one can at least see what might be worth trying as input?

If there isn't such a thing yet (and even if so), I also hope for a continued series on this topic, but please also follow up on the "basic" search features / syntax.

I believe there will be a overwhelming appreciation in the community.

Regards,

Stefan

0 Likes
1,129 Views

Stefan

The ampersand:

Yes, the ampersand is indeed a logical operator (which incidentally can be replaced by the word "and").

Comprehensive documentation:

The compound search using brackets is still fairly new and I admit that it has not been well documented. Neither have some of the other search methods... We need to fix that, I agree. We are currently re-writing a lot and the technical writers and I have an ongoing communication. So I hope that we can deliver that later. Meanwhile, maybe I should write a Technical Brief on this...

HIC

0 Likes
1,129 Views
Partner
Partner

Dear Henric,

This does relate to your current search blog but it may be more for the advanced contribution everyone is hoping for!

I know  the search allows you to do an associated selection – eg Give me all the suppliers who have purchases > 10,000.   Can you do a selection that says “Give me all the suppliers that make up 80% of total sales” (for the current selection obviously).  

If you can can you help with a requirement I have which is to produce a bar chart with two expressions (dimension will be year)

Expression 1:  Total Number of Suppliers

Expression 2:  Number of suppliers making up 80% of total purchases

I have got as far as thinking we may need to use P() in a Set Modifier but I am not sure what the Search Criteria would be.   I am finding that this is not quite a straightforward as it feels it should be!!

Thanks

Philip

PS - Welcome ideas from any followers too!

0 Likes
1,129 Views

What you are trying to do has many names: Pareto analysis, ABC analysis, etc. Unfortunately, this is not simple. But you should look at the "Pareto Select" under the actions.

HIC

0 Likes
1,129 Views
Partner
Partner

Dear Henirc - I know it is not simple - that is why you are such a good person to ask 🙂  We will keep trying but will still welcome all suggestions - I do think it is a useful thing to be able to do.   Philip

0 Likes
1,129 Views
MVP
MVP

I think it would be helpful (not only for pareto analysis) if one could set the sort order of advanced aggregation to other than load order (e.g. y-value 😉 )

1,129 Views
Partner
Partner

Dear HIC,   Thanks for the pareto select suggestion, however will that not make the selection in all the fields.  I think what I need to be able to do is to make this selection as part of a set.   I could apply the Pareto Select "On any select" but that would change the selection I had just made (dodgy). If I had the second expression in an Alternate State maybe I could trigger the Pareto Select to act on a field in that state?  What does the great HIC Calculation Engine think!  Philip.

0 Likes
1,129 Views
Not applicable

Is it also possible to start associated search from within a multibox? Unlike the listbox there is no chevron to to click on.

And second, how would the associated search string be if I want to select the state which is associated to the cities Fresno AND Napa?

Thanks Hernic for another informative blog.

0 Likes
1,129 Views
sorheg
New Contributor

Jeg er ikke til stede før 09.12.2013.

Merk: Dette er et automatisert svar på meldingen "Re: [QlikView Design

Blog ] - Text searches" du sendte den 06.12.2013 16.41.56.

Dette er det eneste varselet du vil få mens denne personen er borte.

0 Likes
1,129 Views
pennetzdorfer
Contributor III

Maybe my question is out of place in a blog post called "Text searches" But I was wondering if there is a "wildcard for numeric characters".

At the moment I'm using the Advanced Search function as a workaround.

Example: I'm looking for all values starting with "HF" and ending with three numbers:

=WildMatch(MyField,'HF*') and IsNum(Right(MyField,3))

That works, but of course a numeric wildcard would simplify things for end users.

Regards,

Florian

0 Likes
1,129 Views
Not applicable

Great post.

0 Likes
1,129 Views
Partner
Partner

Would it be possible to use a logical operator in the search syntax to exclude certain values?

For example, return all "car" values, but not where "hire" is also in the value.

Car Hire - No

Car - Yes

Hire - No

Hire Boat Car – No

Something like: ("car" &! "hire")

I can do this with an advanced search:

=wildmatch(F1,'*car*') and not wildmatch(F1,'*hire*')

1,129 Views
Partner
Partner

Use the - sign as logical NOT

Use the + as logical AND

Use space as OR

("car" - "hire") will find all values with car, but not where hire is also in the value.

0 Likes
1,129 Views
Not applicable

Hi Henric,

I am using "("S*"&"SH")"|"Vivio" to select 2 values from the list box which has the below values. But the | operator does not work.

value1: SH

value2: Shree Ganesh (Notice that this starts and ends with sh)

value3: Vivio

I need to write a search string which only selects value1 and 2 in one go. Need suggestions?

0 Likes
1,129 Views
Not applicable

Hi Henric,

I am using "("S*"&"SH")"|"Vivio" to select 2 values from the list box which has the below values. But the | operator does not work.

value1: SH

value2: Shree Ganesh (Notice that this starts and ends with sh)

value3: Vivio

I need to write a search string which only selects value1 and 2 in one go. Need suggestions?

0 Likes
1,129 Views

Thank you Henric

0 Likes
1,129 Views
richbyard
New Contributor III

Hi Henric,

Great article but I have one additional question for you... How do you ensure that the search string is only going to pick the exact string you enter? for example, when I type "1" in quote marks (and I've tried a number of other syntax options as well) with any search setting on the document I get multiple values returned where 1 exists as the first character. I only want to pass the exact value "1".

Capture.JPG

Would appreciate any advice you can offer.

Thanks
Richard

0 Likes
1,129 Views