Incep()tion
--
Not so long ago I wrote about my experiences deploying a Qlik Core solution to Production for the first time — which you can read here. A part of that particular project allowed me to brush up on my Qlik expression skills, which is something I don’t do all that often these days at Websy. I typically just create solutions in JavaScript and consume content from Qlik created by others.
I’m glad I got the chance to put on a different hat. It’s always nice to rekindle old or forgotten skills and it reminded me of just how powerful the Qlik Engine is.
Let’s jump right in to my particular problem. Identifying related items, which in this dataset means parent, child and sibling records. All of these records are stored in the same table and the only column we have that identifies a relationship is ‘Child’. Imagine it looks something like the following. It’s been simplified to help visualise the logic I’ll be demonstrating:
To get the related records we are going to use the P() function which allows us to retrieve a list of possible values within a given column of data. Let’s take a quick look at the syntax of the P() function.
In my solution, each time the expression evaluates, I’m only working with a single item. For this example, I’ll work with the record ‘Nick’.
So, let’s start simple and look at how to get the parent records. For this we’ll need to get a list of all Names that have a Child value of ‘Nick’. Or, put in P() terms, we want all of the possible Names that would be available if we selected ‘Nick’ in the child column. This is what the P() part of the expression for that would look like.
A quick point before we move on. Providing ‘Name’ inside of the P() in this particular example isn’t changing the behaviour. We could remove it and we’d get the same result. In fact, the P() function isn’t actually necessary for getting the parent records and a set modifier of {$<Child={Nick}>} would be sufficient. However, I’m demonstrating it this way for 2 reasons. Firstly, to keep the examples consistent and, secondly, because I’ll use it later when trying to get sibling records.
Now, let’s wrap it in our set expression. We’ll target the default set ‘$’ and reduce the list of Name values by using the P() expression above, like this:
Getting the child records is essentially the same thing, we simply switch the fields around in the P() expression to get a list of all possible Child values where Name is ‘Nick’.
Now for the hard part, getting the sibling records. For this we’re going to have to put P() inside of P(). It can be confusing, but I’ll try to explain it as best I can. Ultimately it’s a combination of the 2 expressions we’ve already built. We need to get the parent records and then use those to get their children.
Child of same Parent === Sibling
It’s hard to put into words just how we need to combine our previous 2 expressions. Hopefully this GIF helps demonstrate the logic.
Finally, that P() medley can be used to get a list of all Names, which ultimately represent siblings.
Actually, this also returns the ‘Nick’ record. If we don’t want this we can combine it with another expression to remove that value.
While we’re at it, why don’t we combine it with all of our expressions and build a complete ‘Related Items’ list.
You can see an example of this expression at work in the finished product here. The value that would represent ‘Nick’ is passed in dynamically using the APIs but you could equally use some trickery in Qlik Sense to achieve the same.
