Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Compound Search - demystified

swuehl
Not applicable

Compound Search - demystified

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.

Doc1.jpg

  • 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'.

Doc2.jpg

  • 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):

Doc3.jpg

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


Doc4.jpg

    • 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:

Doc5.jpg

    • 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:

Doc6.jpg

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

Doc9.jpg

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

Doc10.jpg

You can achieve similar without nesting, but take care of the order of your searches and operators:

Doc9.jpg

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

Comments
marcus_sommer
Not applicable

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

swuehl
Not applicable

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

marcus_sommer
Not applicable

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

jagan
Not applicable

Very good explanatory document swuehl.  Learn't some new things from this.

Regards,

Jagan.

manojkulkarni
Not applicable

Good Document to collate all the search related topics & compound search which is really interesting. Learnt new thing from your post.

rwunderlich
Not applicable

Great document. I learned a number of new things!

MarcoWedel
Not applicable

great

I already liked your answer in this thread

What is the logical operator for "NOT" in a search string?

thanks

Gruß

Marco

swuehl
Not applicable

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.

tresesco
Not applicable

Great!

Thanks Stefan for letting us know.

PrashantSangle
Not applicable

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,

Version history
Revision #:
1 of 1
Last update:
‎01-30-2016 07:46 AM
Updated by: