Set Expression in Qlik Cloud

[jonoogle]

Can anyone explain the significance of the set expression used below.

min({<IsFiscalMovement_TF=-1 >}PostingDate)

This is not valid in QlikView but it is valid in QlikCloud. (please note the missing brackets). 

As an extra note. WIth the value -1, it believe it gets all the values, instead if set to anything but -1, it doesn't return anything. Lastly the values in my data set for IsFiscalMovement_TF are 1.

[Oleg_Troyansky]

This is certainly a "fluke" that isn't supposed to work. If everything was working as expected, you'd get a syntax error.

Many times, things that aren't supposed to work, happen to work by accident. For example, for many years it was possible to use single quotes in a search Set Analysis condition, even though it wasn't meant to work. It was a bug, and eventually it was fixed in November 2017. The misconception was so widely spread that they had to made a special provision - if the app was created before November 2017, then the old incorrect syntax would be permitted, and if it was created with a later version, then it wasn't.

Similar issue, I believe, is here. It's not supposed to work without the brackets, but it works by accident. Brackets aside, however, the rest is "working as expected". The minus sign serves as a Boolean NOT. So, the condition is to select all values BUT 1. If you are saying that all of your flag values are 1, then I'd expect to receive an empty set in return.

Cheers,

[jonoogle]

Thanks Oleg. I agree it's a fluke. The values all are "1". If the expression is 

min({<IsFiscalMovement_TF=-1 >}PostingDate)

 it uses all the values. Also normally I think the negation would be -= and not =-
On the other hand if I use the expression

min({<IsFiscalMovement_TF=1 >}PostingDate)

I get nothing.

instead 

min({<IsFiscalMovement_TF={1} >}PostingDate)

works as expected. 

It is very strange indeed. This all came out when we migrated an app from QlikView to QlikCloud.  The data was derived from a bit field, and it looks like bit field when pulled from SQL in QlikView was 0/-1 and in Cloud is 0/1.

[Oleg_Troyansky]

Hi,

I usually don't bother understanding why certain incorrect syntax work one way or another, as soon as I know that it's incorrect.

Let me clarify the subtle difference between "-=" and "=-". I teach that example in my Set Analysis session at the Masters Summit for Qlik (which is by the way is coming soon to Hamburg, Germany!)

The "-=" syntax means "modify user selection in this field and remove the following value(s) from it". So, theoretically if there were some selections made in a field, this syntax will mostly respect these selections but remove the specified values from the selected values.

The "=-" syntax actually combines two different symbols (they could be separated by a space, but it's optional). The equals sign "=" means "replace user selections", and the minus "-" means Boolean "NOT". So, if anything was selected in the field, those selections would get ignored and replaced with the set of all values, except for the specified ones.

In your case, no selections were made in the flag, so the two options would render identical results - that is, if the rest of the syntax is correct.

Cheers,

[jonoogle · Accepted solution]

@Oleg_Troyansky thanks so much for delving into this for us. I also contacted Qlik support and here is an excerpt of their response which explains more:

"An explanation of why this is occuring would be due to Qlik Cloud (and newer Qlik sense versions) possessing more leniency in the parser allowing some legacy or non-standard syntax that still resolves correctly during evaluation."

"Without the curly braces, the parser may interpret this as:

A comparison: IsFiscalMovement_TF = -1

But since this syntax is not strictly correct for set expressions, Qlik might treat it as a sort of fallback to "include everything" or result in undefined behavior.

In your statements please see below results and possible reasonings:
min({<IsFiscalMovement_TF=-1>} PostingDate) → Returns values, possibly all or incorrect ones.

min({<IsFiscalMovement_TF=1>} PostingDate) → Returns no values, because 1 is treated as a field instead of a literal value.

min({<IsFiscalMovement_TF={1}>} PostingDate) → Returns correct filtered values, as expected. 

"