topic Set analysis modifiers without quotes in QlikView

Set analysis modifiers without quotes

Vegar — Sat, 16 Nov 2024 19:15:41 GMT

This is a more general question about set analysis modifier behaviour. I've always believed that string modifiers in a set analysis have to be within quotes, like this
Sum({<Color={'Green'}>}Amount)

and for modifiers with numeric value the quotes where optional like this
Sum({<ColorID={1}>}Amount) OR Sum({<ColorID={'1'}>}Amount)

But I noticed that the two expressions below returns the same result.

Sum({<Color={'Green'}>}Amount) AND
Sum({<Color={Green} >}Amount)

Is this an correct observation? Is this new or old behaviour? I notice that the examples in the Qlik help page Set analysis and set expressions are ambivalent in the use of ' ' or not ' '.

Does anyone have a link to Qlik Help och Qlik Support that describes the use of quotes in modifers? I assume that QlikView/Qlik Sense intepret the two differently hence:

Sum({<Color={'*'}>}Amount) is not equal to Sum({<Color={*}>}Amount)

Data sample from the attached qvw file:

DATA: LOAD * INLINE [ ColorID, Color, Amount 1, Green, 100 2, Red, 200 3, Yellow,300 0, *, 400 ];

Do you @Brett_Bleess or @rwunderlich know anything about this set modifier behaviour/intepretion?

Re: Set analysis modifiers without quotes

Brett_Bleess — Tue, 21 Jan 2020 21:16:32 GMT

Hey dude, have a look at HIC's Design Blog post, I am hoping that may explain things, if not, let me know, and I can reach out to him to see if he can chime in on this one for you. Rob may have something too, but I know he is in and out at the moment, so may be a few days before he catches things.

https://community.qlik.com/t5/Qlik-Design-Blog/Quotes-in-Set-Analysis/ba-p/1471824

Cheers,
Brett

Re: Set analysis modifiers without quotes

Vegar — Tue, 21 Jan 2020 22:31:30 GMT

I've seen HIC's post, but it does not really cover my question. He is uses single quotes on all hardcoded string modifiers and no quotes on hadcoded numeric values.

HIC wrote:

Examples of hard coded lists:

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

It would be great if we could get an comment from HIC on the use of

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

Re: Set analysis modifiers without quotes

marcus_sommer — Wed, 22 Jan 2020 14:44:42 GMT

HIC used it correct - by wrapping string values explicitly with single-quotes and the numeric values without quotes. It's a kind of toleration from the parser/engine that it didn't run into an error if there are no quoting around a string-value - but this is only TRUE if the string didn't contained a space or any other kind of special chars, like +, - and so on.

- Marcus

Re: Set analysis modifiers without quotes

rwunderlich — Wed, 22 Jan 2020 15:28:52 GMT

While unquoted strings may "work" for literals I think it's a mistake to leave off the quotes, even for numeric values.

(note my comments below are for the latest releases, behavior on older releases can be somewhat different per HIC's post)

An unquoted string is the same as double quotes, a case insensitive comparison.

{one} = One
{"one") = One
{'one'} ≠ One

If the string contains spaces or special characters, you must use quotes. An exception to this rule is the wildcard characters * or ?. By themselves, they will work without quotes., although some versions will flag as syntax error.

Set Analysis comparison is always string based, so leaving quotes off of a numeric value gains no advantage or special behavior.

{2} = 2
{"2") = 2
{'2') = 2
{02) ≠ 2
{"02"} ≠ 2

When testing a Dual value, you must always use the string value. Given a Dual('One', 1):

{one} =
{1} ≠
{"1"} ≠

So I believe the best standard is to always quote, even numerics. I sometimes leave quotes off numerics because it's what people expect and the fewer characters I type generally the fewer mistakes I make. But I still believe it's the best practice to always explicitly quote.

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-modifiers.htm

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Set analysis modifiers without quotes

martinpohl — Wed, 22 Jan 2020 15:38:41 GMT

sse also here

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-modifiers.htm

In previous versions of QlikView, there was no distinction between single quotes and double quotes and all quoted strings were treated as searches. To maintain backward compatibility, documents created with older versions of QlikView will continue to work as they did in previous versions. Documents created with QlikView November 2017 or later will respect the difference between the two types of quotes.

Regards

Re: Set analysis modifiers without quotes

Vegar — Wed, 22 Jan 2020 21:10:39 GMT

Thanks for the replies @Brett_Bleess , @rwunderlich , @marcus_sommer and @martinpohl

I found the section below in the linked Set Modifiers page.

I understand it as Qlik tries to treat listed values in modifiers using quotes and not using quotes as equal, but some kind of exception is done when using wildchard * and ?. The expression Sum({<Color={*}>}Amount) is not treated as single quote, but double Sum({<Color={"*"}>}Amount).

I also payed notice to Rob's words "Set Analysis comparison is always string based, so leaving quotes off of a numeric value gains no advantage or special behavior".

Because of the Qlik explanation, my observations and Rob's words I will stick to my current single quote usage for all string fixed values and I'll even try to start using them on numeric values as well.

Thanks again for your help guys

BR
Vegar

Re: Set analysis modifiers without quotes

rwunderlich — Thu, 23 Jan 2020 03:44:59 GMT

Not that unquoted string is treated as double quote, not single quote.

{*} = {"*")

{one] = {"one"}

-Rob

Re: Set analysis modifiers without quotes

Vegar — Thu, 23 Jan 2020 10:18:16 GMT

Yes, you're right. Thanks for pointing this out.

My mind where locked into my old truth, not seeing what lied before me. It even makes more sense this way, and there are no exceptions for wildchars.