Hello my dear readers, aka Qlik
addicts geeks who prefer reading my ramblings about Qlik over sharing funny cat memes on Facebook!
Today, I’d like to talk about a (somewhat) new feature that allows Qlik Sense users (the cool kids that can author their own measures and expressions) to insert Set Analysis conditions automatically, without the need to worry about its quirky syntax. Sounds pretty cool, huh?
You may have noticed that some new options appeared in the sidebar of the Expression Editor about a year ago. Notice the parts highlighted in yellow.
I have to admit, I hardly pay attention to these helper tools because, like many old-school programmers, I find the process of typing everything with my own fingers to be faster than tinkering with several drop-down boxes and selection options using the mouse.
So I didn’t notice anything new until Rob Wunderlich – my colleague at the Masters Summit for Qlik, the legendary Man in a hat, the author of QSDA Pro, etc. etc. (see full list of Rob’s titles in Appendix 1) – mentioned to me that I should check it out, because it might soon put me out of business of teaching advanced Set Analysis at the aforementioned Masters Summit for Qlik.
By the way, did you know that we are coming soon to some of the happiest places on Earth? In November, we are going to have a Summit in Orlando, which is known to be THE happiest place on Earth. And before that, in October, we are going to be in Dublin, which may not have Disney parks, but makes up the difference with large quantities of locally produced spirits. Check it out, it will be the best business trip one could wish for! But I digress…
Back to the “Insert Set Expression” feature. You can see that it’s available in two flavors – as an addition to the Field selection and as a feature of its own, under the tab “Set Expressions”. As the friendly comment suggests, we need to make selections in the app to enable Set Expression. I followed their advice and selected a few values in a couple of fields. Then, back in the Expression Editor, I selected the “Set Expression” checkbox, pressed on “Insert” and voila – the new expression with the desired Set Analysis was generated for me:
So, this is pretty cool, I have to admit! Let’s start digging deeper. How about a simple search? For example, can I select Styles that contain the word “Jacket” in them? Let’s see… I had to open the Selections Tool before I could make a Search selection (something strange happened with the Filter Pane, but that’s a different story altogether). After selecting “*Jacket*” in the Search box, I can see that the Selection bar shows “3 out of 14”, and the three Styles are selected.
So, now the biggest question is this – when I insert this Set Expression in the editor, will I see the three selected styles, or will Qlik Sense preserve my original search “*Jacket*”? Place your bets, ladies and gentlemen! Back to the Expression Editor, comment the previous version, press on Insert, and voila – the search gets inserted in its original form. Bravo!
Now, how about excluded values? What if I need to select all Styles EXCEPT Jackets? I’ll use the “Select Excluded” feature, and then generate the expression again.
Hmm, this is a bit disappointing… The original search got lost and replaced with a hard-coded list of all excluded values. Also, occasional single quotes in the Style names didn’t get properly handled, causing a syntax error. Note to self: to generate an exclusion, formulate the positive condition first, and then add the Boolean NOT manually. And, remember to take care of any occasional quotes in the text.
Now, what about Advanced Search, aka Expression Search? Would that be possible? How about selecting Customers that spent more than $20,000 on our goods? I’ll type the following condition in the Search box for Customers and see what happens:
Once again, bravo! The Advanced Search condition got populated very nicely, as intended! Admittedly, developers who can type this condition in the search box with no helper tools (wink-wink, Qlik) should be able to ride their Set Analysis bikes without training wheels, but still. This is pretty cool!
Last test before we part ways… What about Compound Search? Will that work in the generated Set Expression? You bet!
This Compound Search condition selects Styles that contain one of the two words in the name – either Jacket or Polo. The syntax, as you see above, requires that the two wildcards are enclosed in parentheses and separated by the Pipe symbol. The condition gets inserted into the expression “as is”. That surprised me at first. Naively, I pictured something like the Boolean Union being generated, like in the second expression above – that’s how I’d formulate this search if I wrote this Set Expression manually. As you can see, both expressions produced identical results, so the difference is purely semantic.
In conclusion, I’d like to applaud Qlik for creating a nice helper tool, that will certainly help beginners to wrap their mind around Set Analysis more easily. Having said that, I think I don’t need to worry about losing my job as a Set Analysis teacher. For complex business analytics, there is still no replacement for solid knowledge of the material – that is, until they train ChatGPT to produce reliable Set Expressions based on natural language and voice intonations. Until then – check your autumn schedule and come learn advanced Set Analysis at the Masters Summit for Qlik. Did I mention that we are coming to Orlando? And Dublin? C’mon, guys – it doesn’t get any better than that!