Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Search - But what shall you find?

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
luciancotea
Valued Contributor

Valuable insights from HIC, as always!

One thing:

"You enter a string, and QlikView immediately searches..."

Could there be added a short delay so I can get the chance of typing more letters? When searching in big files it takes forever to pass the first letter.

0 Likes
74 Views
Not applicable

Great and useful post!

One question remains for me: why does Qlikview choose to let developers explicitly define the number representation (decimal and thousand separator, time format etc) in the script, but use the regional settings for collation? To me it would make more sense to either define both in script or get both from the regional settings

74 Views
Employee
Employee

I agree that it would make sense to have an environment variable for the collation also. I really don't know why there isn't one already. It's only recently that we have become aware of this "anomaly". We just haven't thought of it earlier, I guess...

HIC

PS I just spoke to our main developer about this, and his answer was that it has historical reasons: The collation information of other regional settings than the current, was just not available in earlier versions of Windows. So, at the time, it didn't make sense to have a variable for collation.

74 Views
Not applicable

Great post..

About regional settings, it would be a big advantage if the user could choose the number and date representation in the qvw file. For example a US user vs EU user for one file that log into the same file and choose the regional settings via a variable.

0 Likes
74 Views
MVP
MVP

Thanks Henric, very good insights! How this can work in a multilingual company / environment if it is load based ("This information is then stored into the qvw file when the script is run.")?

74 Views
simondachstr
Valued Contributor III

"You enter a string, and QlikView immediately searches in the active list box and displays the matches."

You're entirely correct and this is a major issue. In bigger applications this makes search boxes become completely useless (loading times of over 10 seconds before I can type the second letter). And in big applications with many columns and rows is where you need the search functionality the most.

0 Likes
74 Views
Employee
Employee

If you with "multilingual" mean that you'd want different collations for different users, my answer is: It can't. The evaluation is done by the engine in the back-end, and it works independently of the user settings.

And I am not sure that you'd want it to work differently. It would imply that a bookmark containing a search would result in different selections in Sweden and Germany. Imagine the confusion if we were to call each other and discuss the result of the bookmark selection...

HIC

0 Likes
74 Views
MVP
MVP

Would be good to have threshold to start search after 2-3 characters or so..

0 Likes
74 Views
MVP
MVP

You're right. But then it could be difficult to find names in a multilingual scenario considering local branches..

0 Likes
74 Views
jerrysvensson
Valued Contributor II

Yeah we found this about a year ago.

The accounts welul and velul was considered the same.

0 Likes
74 Views
Or
Valued Contributor II

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
74 Views
dmohanty
Valued Contributor

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

Nice to know about a new concept.

Thanks HIC

0 Likes
74 Views

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
74 Views
Employee
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
74 Views
luciancotea
Valued Contributor

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

74 Views
Not applicable

Hi Lucian

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

0 Likes
74 Views
simondachstr
Valued Contributor III

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

0 Likes
74 Views
juraj_misina
Valued Contributor

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
74 Views
quiquehm
New 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
74 Views
simondachstr
Valued Contributor III

Preferences -> Email Preferences ...?

0 Likes
74 Views
Employee
Employee

Juraj Misina

This looks like a bug to me... Of course it should respect the regional settings. I have reported it.

HIC

0 Likes
74 Views
Not applicable

Thanks Henric.
This is important for latin-based languages too, and their users (like me) must know how to work with collation in qv.

Regards.

0 Likes
74 Views
Not applicable

Perfect!

Henric: Is it possible that you could write a blog about ranking/autonumber. If think this is seen alot and quite useful function! Hope you can clarify  things on this subject!

0 Likes
74 Views
isingh30
Contributor III

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

Henric...how can we change collation? Like this  - SET CollationLocale='es-VE';

Thank you!

0 Likes
74 Views
Employee
Employee

This blog post was written 4 years ago, and then the only way to change the collation was to change the Windows settings and restart QlikView. This is still the only way to change the collation for QlikView 11.

But we have since then introduced (Qlik Sense 3 and QlikView 12) an environment variable CollationLocale so you can change it in the script, the way you suggest:

SET CollationLocale='es-VE';


HIC

0 Likes
74 Views
isingh30
Contributor III

Thank you! Much appreciated.

0 Likes
74 Views