Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Searching in field values is a powerful feature in QlikView (all this should also work in QlikSense, but I haven't tested everything). You can use searches for example in set analysis field modifier, select in field actions and not to forget filtering e.g. list boxes in the frontend.
Hence knowing the power of searches is vital to developers as well as end users.
Unfortunately (in terms of 'getting started with search'), there are several possible search modes, have a look at The Search String to get an overview.
Text searches continues explaining. Some of the search modes are explained a bit more detailed: The Expression Search
And there are some things to consider we would hardly know without following Henric Cronström's ( hic ) great design blog: Search - But what shall you find?
Not to forget, there is a nice cheat sheet: Qlik Sense Search Cheat Sheet, covering some features, but not everything.
There is also of course some documentation in the HELP file as well as the reference manual, but not what I would call a comprehensive documentation of this essential product feature. Especially, there is few documentation about the so called 'compound search'.
So let us shed some light on
QlikView's Compound Search feature
First, let's create some sample data:
SAMPLE:
LOAD RecNo() as Number, Text
INLINE [
Text
Harry
Sally
Harry & Sally
New Hampshire
New York
Something new
UPPER
not upper
Al Bundy
Airport Newark
me
];
- Triggering a compound search
A compound search is triggered by enclosing the search filter in parentheses (...)
The search itself will be performed over the values in the field's symbol table.
- The most simple possible search filter is just inputting a value. In contrary to other search modes, the compound search will search for values that exactely match your search term.
[Note: A case insensitive comparison will be performed, but according to some comments by Henric in above blog posts, this is considered a bug and may be fixed sometime in the future ]
For example, (1) put into a Number field list box search bar will return only the exact match '1', not all values that contain '1' or start with '1'. Another example: Try upper compaired to (upper) in Text list box search bar.
- Wildcards
You can use wildcards in a compound search, too. A question mark '?' is a placeholder for any single character, a star symbol '*' for any number of not defined characters.
(?e) will return any value with two characters, second being an 'e'. (*e) will return any value that ends with an 'e'.
- Quoting
If your values contain spaces, you need to quote the value: ("Something New") will find the value, (Something New) not.
- Wildcard ^: Beginning of word
If you quote your search string, you can also use the wildcard '^' to match your search term with beginning of words in your values
(read further down why you need to quote here, even when there is no space contained):
[Note: As Henric commented to one of his blog posts, there is no escape character to escape a wildcard, hence if you want to search for e.g. '?', you would need another search mode, e.g. advanced search using =index(FIELD,'?') ]
- Combining searches using search operators:
Now it's getting a little bit more advanced: If you want to create some more complex searches, you can combine two or more searches with search operators:- OR operator '|'
Using this operator, your compound search will return values that match either the first or the second search, e.g.
- OR operator '|'
- AND operator '&'
Using this operator, your compound search will return values that match both first and second search, obviously, this does only make sense if you are using wildcards:
- Up to this point, all this can be found here in the forum, in Henric's design blog referenced above, in the comments to his blog, in the tech doc or in some other threads. But I hope this document will add something useful to all this already available, though distributed material. Introducing the AFAIK not really well-known
XOR operator '^'
Using this operator, your compound search will return values that match either the first or the second search, but not both:
Another example for the XOR operator would be to find the complement of a search (for example search for *Sally*):
You could achieve this by selecting (Sally), then use the context menu to select excluded, but you can also use a single search: (* ^ *Sally*)
[Agreed, the syntax would be easier if there was an operator for NOT or complement, but I haven't found it so far. If you know it, please drop a comment below.]
All these operators can be used to not only combine two, but many searches: (me|Harry|Sally)
This would be a good point to end this document. But there is something more I haven't read about so far:
- Nesting compound searches
You can nest a compound search into another compound search. Why you may want to do this?
For example if you want to create a search like 'Return all values containing 'Harry', but exclude values where 'Sally' appears.
Since to my limited knowledge, there is no 'NOT' or complement unary search operator, you can emulate this search using the existing three search operators described above:
You can achieve similar without nesting, but take care of the order of your searches and operators:
Some (or all?) of these results can of course be achieved using other search modes, I just wanted to focus on the compound search mode in this blog post. I hope all mysteries of the compound search have been revealed.
If you have any comments or questions, drop a comment below.
Have fun!
edits:
2016-02-02: Complement can be created easier, just using (* ^ *Sally*), added some more examples, reorganized some parts
Hi,
I have tested below cases in your example
1: if we just search with -h or -H It will exclude all the value which is started with h/H
It is excluding "Harry" , "Harry & Sally" and "New Hampshire"
and Modify to above search with -Har. It is excluding only those value which is started with har
It is excluding "Harry" , "Harry & Sally" and not "New Hampshire"
Also if we want to exclude multiple value then we can exclude it like
-string1 -string2
2nd way is already suggested by Tresesco
Regards
Stefan,
I never intend to kill someone whom I admire. 
After testing a bit more I can only agree with you and post some results as you explained.
'Like' alone doesn't work with wildcard but does without it.
NOT seems to work only in combination with LIKE and wildcards. May be Henric hic would have some pattern to suggest.
Thanks.
.png "Luminary Alumni"){kind=icon}
