Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

The search functionality is central to QlikView. You enter a string, and QlikView immediately searches in the active list box and displays the matches. But what really defines a match? For example, should you find strings containing ‘Š’ when your search string contains an ‘S’? Or ‘Ä’ when you search for ‘A’?

These may be odd questions for people with English as first language, but for the rest of us who use “strange” characters daily, these questions are important as the answers affect not just search results, but also sort orders.

It is called Collation.

A collation algorithm defines a process of how to compare two given character strings and decide if they match and also which string should come before the other. So, the collation affects everything from which search result you get in a query, to how the phone directory is sorted.

Basically the collation is defined differently in different languages. Examples:

  • The English collation considers A, Å and Ä to be variants of the same letter (matching in searches and sorted together), but the Swedish collation does the opposite: it considers them to be different letters.
  • The English collation considers V and W to be different letters (not matching, and not sorted together), but the Swedish collation does the opposite: it considers them to be variants of the same letter.
  • Most Slavic languages consider S and Š to be different letters, whereas most other languages consider them to be variants of the same letter.
  • In German, Ö is considered to be a variant of O, but in Nordic and Turkish languages it is considered a separate letter.
  • In most western languages I is the upper case version of i, but in Turkish languages, I is the upper case of dotless ı, and İ (dotted) is the upper case of dotted i.

An example of how these differences affect sort orders and search results can be seen in the pictures below:

English.png   Swedish.png

The search string is the same in both cases, and should match all field values that have words beginning with ‘a’ or ‘v’. Note that sort orders as well as search results differ.

Hence: A number of differences exist between languages that have special characters or characters with diacritic marks, e.g. Å, Ä Ö, Æ, Ø, Þ, Ś, Ł, Î, Č. Sometimes these characters are considered as separate letters, sometimes not. Some languages even have collation rules for letter combinations and for where in the word an accent is found. An overview can be found on Wikipedia.

So, how does QlikView handle this?

When QlikView is started, the collation information is fetched from the regional settings of the operating system. This information is then stored into the qvw file when the script is run.

Locale.png

Usually you don’t need to think about this, but should you want to test it yourself, just change the regional settings in the control panel (the Formats tab – not the Location tab), restart QlikView, and run the script of your application.

Bottom line – should you need to change the collation, you should do it on the computer where the script is run.

HIC

Further reading related to this topic:

Text searches

The Search String

The Expression Search

26 Comments
Or
MVP
MVP

I'd also like to chime in with a request for improved decision-making on when to start searching. Currently my resolution for this is to remove all large value lists from the searchable fields, but in addition to making it impossible to search on those fields - which is sometimes a requirement - it also means I have to manually adjust every search object I use and I have to remember to add new fields every time I modify the QVW (why we don't have an "Exclude fields" option in addition to the "Select fields", I know not).

0 Likes
1,934 Views
dmohanty
Partner - Specialist
Partner - Specialist

As always, it made an excellent read and learning!!

Nice to know about a new concept.

Thanks HIC

0 Likes
1,934 Views
Nicole-Smith

What about different collations for different .qvw files on the same server?  It seems like this isn't possible since it goes off of whatever Windows is set to.

0 Likes
1,977 Views
hic
Former Employee
Former Employee

It should work. But you need to run the scripts under different regional settings, i.e. either have two execution services on different machines; or run one, change the settings, and then run the other. Not very practical, I admit. That's why it would be good to have a variable for the collation...

HIC

0 Likes
1,977 Views
luciancotea
Specialist
Specialist

orsh_: I was facing the same problem. What I did is to use a button which switched between 2 different overlapped search boxes:

- "Quick Search": search only specific fields

- "Global Search". search all fields

QuickSearch.jpg

1,977 Views
Not applicable

Hi Lucian

Thank you for offer good solution , which i am looking for.

0 Likes
1,977 Views
simondachstr
Luminary Alumni
Luminary Alumni

Good idea! In addition to that, a dynamic way to do your Quick Search:
http://community.qlik.com/docs/DOC-5461

0 Likes
1,977 Views
juraj_misina
Luminary Alumni
Luminary Alumni

Thanks for insightful post Henric.

Is there any way to affect collation of field names? If I have fields with "strange" first letter, they are always on the end of the list of fields (see last to fields on picture below).

Thanks.

FieldList-collation.PNG.png

0 Likes
1,977 Views
quiquehm
Contributor III
Contributor III

Guys, I am not sure how I setup this blog to feed emails to my inbox…could you please let me know how to stop it ? I am a qlikview user and follow the stuff in the community but I would like to stop these email notifications to my work email …inbox is piling up ☹ . I am sure I changed any settings in my profile in the QlikCommunity ..by mistake.

Appreciate any help

Thanks

0 Likes
1,939 Views
simondachstr
Luminary Alumni
Luminary Alumni

Preferences -> Email Preferences ...?

0 Likes
1,939 Views