Qlik Community

Qlik Design Blog

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

Quotes in Set Analysis

Today, the Qlik engine has some bugs in the area of a search and a subsequent select. These affect both interactive searches and searches in Set Analysis expressions. We are working on fixing them. However, one of these coming bug fixes may cause some backward incompatibility. This post explains what the bug fix will mean, and what you can do to avoid future problems.

When you use Set analysis, you can choose to have a condition in the form of a list of values in the Set expression, or you can choose to have a rule-based definition - a search for field values.

 

Examples of hard coded lists:

 

Sum( {$<Year = {2013,2014,2015}>} Sales)

Sum( {$<Country = {'Australia','Canada','France'}>} Sales)

 

Examples of searches:

 

Sum( {$<Year = {">=2013"}>} Sales)

Sum( {$<Country = {"Austr*"}>} Sales)

Sum( {$<Customer = {"=Sum(Sales)>100000"}>} Sales)

 

All the above constructions work today, and they will work correctly also after the bug fix.

 

Note the double quotes in the search expressions. These define the search strings, and between them you can write any search string – the same way as you would in a list box search.

 

However, you should not use single quotes to define a search string.

 

Single quotes are reserved for literals, i.e. exact matches. Hence they should be used when you want to make a list of valid field values, like the above list of explicit countries. But they should not be used for searches. Single quotes imply a case sensitive match with a single field value.

 

This difference between single and double quotes has been correctly described in the reference manual that states that searches are “always defined by the use of double quotes”. Also, all examples with literal field values in the documentation use single quotes.

 

Now to the bug: QlikView and Qlik Sense currently do not respect the above difference between single and double quotes. Instead, strings enclosed in single quotes are interpreted as search strings. As a consequence, it is not possible to make case sensitive matches with field values, something which you sometimes want to do.

 

This bug will be fixed in the coming November releases of Qlik Sense and QlikView. Then, wildcards in strings enclosed by single quotes will no longer be considered as wildcards. Instead, the strings will be interpreted as literals, i.e. the engine will try to match the string with a field value containing the ‘*’ character. The same logic will apply also to relational operators and equals signs.

 

Unfortunately, this bug has been incorrectly utilized by some developers: I have seen Set Analysis expressions with correct search strings, but erroneously enclosed in single quotes; for example

 

Sum( {$<Country = {'Austr*'}>} Sales)

 

This search should instead have been written

 

Sum( {$<Country = {"Austr*"}>} Sales)

 

Hence, there are documents with expressions that will not work in the corrected algorithm. However, the bug fix will be implemented in such a way that old documents will use the old logic, and new documents will use the new logic. In addition, it will be possible to force the new logic for all documents by using a parameter in Settings.ini.

 

You can of course also change the single quotes in existing search strings to double quotes, and the expression will continue to do what it always has done.

 

This post is mainly to give you a heads-up on a coming change, and give you a chance to make a quality control of your own documents. We will publish more information as we get closer to the release.

 

HIC

 

Further information related to Set Analysis and Searches:

 

The Search String

Introduction to Set Analysis (video) - Part 1

Set Analysis - Part 2 - Cool Stuff You Can Do (video)

60 Comments
MVP
MVP

Thanks a lot Henric, for the update and news. Finally case sensitive comparison would be smooth in set analysis. Many literal searching would not require unnecessary workarounds anymore.

1,510 Views
kkkumar82
Valued Contributor III

Thanks Henric for the important update

0 Likes
1,510 Views
Employee
Employee

Thanks for the update.
Forwarding it to my network.

0 Likes
1,510 Views

Liked it !! But i am thinking how to know which values are getting correct and/or not. Example,

Sum({<Country = {Brazil}>} Sales) and Sum({<Country = {'Brazil'}>} Sales) returns the same values and tested the same till 5 M rows. Can we discuss about this part..

0 Likes
1,510 Views

In the current version they will both result in the same numbers. That's the point.

But in the future Sum({<Country = {Brazil}>} Sales) will match BRAZIL but Sum({<Country = {'Brazil'}>} Sales) will not.


The first should be case insensitive and the second should be case sensitive.


HIC

1,510 Views

Just to make it crystal clear:

No quotes: Case insensitive. Asterisk is not allowed.

Single quotes: Case sensitive. Asterisk is interpreted as Char(42).

Double quotes: Case insensitive. Asterisk is interpreted as a wildcard.

HIC

1,510 Views

True, Here Sum({<Country = {Brazil}>} Sales) // This case, If i want to get sales for BRAZIL Country then if we write something like below. It works as we state, Isn't it? Sum({<Country = {brazil}>} Sales)

When this functionality/algorithm coming? This November released version??

0 Likes
1,510 Views

Yes, our current plan is to have this in the November releases.

1,510 Views
MVP & Luminary
MVP & Luminary

This has also implications if you need to nest set modifiers.


This won't work anymore:

Sum( {$<Customer = {"=Sum({<Sales={ '>500' }>}Sales)>100000"}>} Sales)


Instead you need to use one of the other valid quote characters like backticks ` or square brackets [ ]

Sum( {$<Customer = {"=Sum({<Sales={ [>500] }>}Sales)>100000"}>} Sales)

Sum( {$<Customer = {"=Sum({<Sales={ `>500` }>}Sales)>100000"}>} Sales)

1,510 Views

Impressive !!!

1,510 Views
rajareddyjkl
New Contributor III

Thanks Henric ...

0 Likes
1,510 Views
roharoha
Valued Contributor III

, ... really great!

0 Likes
1,510 Views

Wow, this will make our life somewhat difficult in a way that we might need to go back to fix a lot of places .... I totally didn't think about this

0 Likes
1,510 Views
vgutkovsky
Honored Contributor II

Thanks, Henric. Can you please expand on this:

However, the bug fix will be implemented in such a way that old documents will use the old logic, and new documents will use the new logic

Is an "old" document one that was originally created with an earlier version of QlikView, even if it is subsequently modified with QlikView 12.10? Under what circumstances would the old syntax in "old" documents stop working? What about user-created expressions that are housed in Shared files, would those always be considered "new" even if the Shared file belongs to an "old" document?

I'm particularly concerned with the functionality break that Gysbert mentioned above. I probably have thousands of expressions in production among my various clients that use simple nested single-quote searches.

Thanks,

Vlad

1,510 Views

gwassenaar , vgutkovsky and stalwar1 :

You will not need to change old documents.

Each document contains information about which version that created it. And this information will be used to determine which logic to use. It will not matter if you modify it, change expressions or add collaboration objects. An "old" document will remain old.

1,510 Views

gwassenaar

You are absolutely right that if you have nested quotations, you may need to use all three quoting possibilities: "..", [..] and `..`. All three can be used to define a search.

1,510 Views
MVP
MVP

That's a big relief.

Each document contains information about which version that created it.

Wow! Thanks Henric; thanks Qlik.

1,510 Views

Oh wow!! this is a huge relief!! Thanks for the clarification hic

0 Likes
1,510 Views
godfreydaniels
New Contributor III

Each document contains information about which version that created it. And this information will be used to determine which logic to use. It will not matter if you modify it, change expressions or add collaboration objects. An "old" document will remain old.

Besides the forthcoming bug fix, how else is this version info used?  If I add the same expression to two documents on the same day--one old, one new--it seems I cannot expect it to behave the same in both.

0 Likes
1,510 Views
vgutkovsky
Honored Contributor II

That's a relief thanks! When you wrote that the purpose of this post was to "give you a chance to make a quality control of your own documents" I got a little scared.

Follow-up questions:

1. What does "old" mean? Docs created in version 11.20? Or even earlier releases of 12?

2. Since QlikView clearly has the ability to decide which syntax to apply to which documents, does that mean we can use settings.ini to force even new documents to the old syntax?

Thanks,

Vlad

0 Likes
1,510 Views
MVP
MVP

No quotes: Case insensitive. Asterisk is not allowed.

Single quotes: Case sensitive. Asterisk is interpreted as Char(42).

Double quotes: Case insensitive. Asterisk is interpreted as a wildcard.

Does this also mean that, we still don't have an option to perform direct 'case sensitive wildcard search' in set analysis? That would have been a great plus.

0 Likes
1,510 Views

perform direct 'case sensitive search' in set analysis?

What would that mean? Isn't Single quotes going to allow you to do case sensitive search?

0 Likes
1,510 Views
MVP
MVP

Sunny, I meant wildcard search.

1,510 Views
xufei123
Valued Contributor

Could anyone clarify one thing please? For the old expressions in the old version of QlikView, after we upgrade the QlikView server to the November 2017 version, will they work or not work.

If they will not work, we will have to check through all the expressions before we upgrade QV, is that correct?

Thanks

0 Likes
1,510 Views
MVP
MVP

xufei123

They WILL work without any changes. This question has already been addressed in saying :

However, the bug fix will be implemented in such a way that old documents will use the old logic, and new documents will use the new logic.

Also, please check the comments above - it has already been discussed.

0 Likes
1,510 Views
MVP & Luminary
MVP & Luminary

You can use string functions like Left, Mid, Right and sometimes Index for that. Not ideal, but not impossible.

0 Likes
1,510 Views
vgutkovsky
Honored Contributor II

tresesco‌ chr(42) would mean that an asterisk would be interpretted as a literal character. So if you write ={'David*'} that would only return records where the asterisk is part of the field value itself. The asterisk won't be treated as a wildcard.

0 Likes
1,510 Views
rva_heldendaten
Contributor III

Hi Henric!

Will this also fix "parameter parsing" when I want to inject a SET ANALYSIS via $1 into a variable?

Today Qlik always splits parameters with ever comma.  Even when I use escape parameters  to hand over for example Year={2015},Country={AT} to  the variable. See examples below

set_analysis_Parameter_Parsing.png

I'm aware of some workaround to overcome the issue [ Use the (A|B|C) Syntax for multiple values; replace comma, hand over a variable that contains the SET]; but still it would be so much easier to use and maintain if Qlik could come up with a solution here.

In my imagination we can at some point use this feature to hand over parameters to MASTER ITEM expressions. This would help so much to provide "SELF Service Experience", without  creating dozens of MASTER ITEMs where just the SET-part is different...

1,510 Views

Not sure. I'll need to investigate.

1,510 Views
MVP
MVP

gwassenaar‌, vgutkovsky

Thanks.

I was actually emphasizing more on

direct 'case sensitive wildcard search'

Alternatives are there I agree. With double quotes, we would be able to search, but that is unfortunately case-insensitive. Case sensitive match (read-'no search') would be possible using single quotes. That way, we are left with only 'case sensitive search (read wildcard)'; and that is not there. Yes, indirect solutions are there.

0 Likes
1,510 Views