Text searches

[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. 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.

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.

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.

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.

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

[#2]

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.

[Anonymous]

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.

[pennetzdorfer]

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

[#5]

Great post.

[francoiswiid]

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*')

[francoiswiid]

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.

[#8]

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?

[#9]

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?

[robert_mika]

Thank you Henric

[richbyard]

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".

Would appreciate any advice you can offer.

Thanks
Richard