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
];
[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,'?') ]
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:
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 Stefan,
a great post. Many thanks for your efforts to collect the various search-topics within one document and to extend the compound search. I learned some new possibilities like the compound search with operators or with nested searches. But I do doubt that they are user-friendly enough for the most users so that some simplification in the usage might be helpful. By this I thought on something like user-defined functions performed through variables like in this case: Re: Handling [dash] and [single quote] by search objects and maybe there are also workarounds possible with the compound search.
Have you practically ideas in these direction?
- Marcus
Marcus,
thank you for reading and commenting on my first document.
Just for clarification: This document is only to provide a somewhat (more) comprehensive tech doc of the compound search mode. I don't want to imply that this search mode is better than one of the other search modes and I already pointed to one or two issues where I see room for improvement (agreed that the syntax gets quite complicated for the standard business user).
My general approach for this tech doc is 'you can only really use a tool if you know it'.
I am not sure if using a variable expansion would be much better in terms of ease-of-use for the user. I would probably rather build a (custom) search object with some input boxes and button actions. Or provide some flags for filtering precalculated in the script.
What is your experience with the variable expansion approach? Do users accept it?
- Stefan
I think you are right and only very few advanced users would be able to apply such a user-defined function implemented per variable-parameters. If I think on the user-trainings which I made only one user from ten could follow very simple search-examples with match/wildmatch (and never used them) and the others switched directly on screensaver when functions were mentioned. It's very pity but so is life ...
- Marcus
Good Document to collate all the search related topics & compound search which is really interesting. Learnt new thing from your post.
Great document. I learned a number of new things!
great
I already liked your answer in this thread
What is the logical operator for "NOT" in a search string?
thanks
Gruß
Marco
Thanks Marco, Rob, Manoj, Jagan and Marcus, appreciated.
I just noticed that the complement search can be simplified to (* ^ Value), and added some more examples.
Great!
Thanks Stefan for letting us know.
Hi,
Awesome ,Really nice article and so much to learn about search
One more thing about NOT Operator I am not sure but - will work as Not.
Regards,