I’d like to open this article with a true story that makes me chuckle every time I recall it… A few years ago, I was at Qonnections (ah, those good ol’ days when we could travel and meet with thousands of friends without worrying about social distancing…) That year at Qonnections, I had the pleasure of teaching a technical session about the Advanced Aggregation function AGGR. Prior to that, I ran into an old friend of mine and a brilliant Qlikkie, who introduced me to Qlik almost 20 years ago. As he ran off to a meeting, he exclaimed, “I’m going to attend your session on AGGR. I’m still a little bit confused by this function!”. I remember this story every time I feel confused about something in Qlik. It’s a reminder for me that it’s OK to be confused about certain things, even after doing something for almost 20 years.
This time, I got confused about a question that Rob Wunderlich had asked me on a Friday afternoon. He asked me (and the other members of the Masters Summit team): “What do you think is the difference between these two expressions:
Count({<Value={“>3<8”}>} Value)
vs.
Count({<Value={“>3 and <8”}>} Value)
and what kind of results would they render?
Well, every student of mine surely knows that the first expression is a valid Search expression, and the second expression is invalid. Simple Search does not allow Boolean operators such as “and” as part of the syntax. So, I surely expected the second expression to fail. It should return an error, or the filter should be ignored altogether, rendering a full Set of data.
But that wasn’t the case, at least according to Rob. “It’s not necessarily invalid”, he said, “I could hear Henric Cronström explain ‘it’s not invalid, it’s misunderstood'”. At that moment, I could hear Henric say that, too.
So, I was sufficiently intrigued, and I decided to test it myself. I loaded a table with field values from 1 to 10, and I entered the two expressions listed above. As expected, the first formula returned numbers between 4 and 7. Much to my surprise, the second, “misunderstood” formula returned numbers from 1 to 7! In other words, the first condition >3 was ignored and the second condition <8 got processed:
I tried replacing the word “and” with any other texts, and I was getting the same result:
I can’t say that I expected this to happen, but now we all know – when a Simple Search condition contains two parts and any unexpected text in the middle, the first part of the condition will get ignored and the second part of the condition will get processed. Not necessarily the most practical discovery, but it’s surely an interesting puzzle to play with on a Friday afternoon…
Now, how about the following expressions:
Count({<Value={“=Value>3 and Value<8”}>} Value)
Count({<Value={“=Value>3 inverse Value<8”}>} Value)
What would you expect to see there? Why don’t you test it out yourself and see if you are as surprised as I was.
In conclusion, let me invite you to my virtual session on Set Analysis and Advanced Aggregation (AGGR) at the virtual Masters Summit for Qlik at Home. We will be delivering our advanced technical training for Qlik professionals virtually, until it’s safe for all of us to meet for a live conference again. Whether you are still a little bit confused by AGGR, or need to understand the difference between Simple Search and Advanced Search – come to my session on November 18th and get your questions answered.
See you there!