272016Oct

Q-Tip #15 – Excluding the Excluded

It’s been a while since I found an interesting topic, worthy of a blog article… This Q-Tip originated from a question that we received at the Masters Summit for Qlik in Ausitn, TX. Here is the question:

We have Accounts and Assets. Each Account can have a different number of Assets. When the user selects any combination of Assets, we need to select only those Accounts that hold the selected assets and don’t hold any other Assets.

Hmm… at first I was puzzled and couldn’t even come up with a direction for the possible solution. How do I select Accounts that hold only the selected Assets and don’t hold any other assets? Several forms of Advanced Search passed by in my mind, but none of them offered a hint to the possible solution.

Then, I recalled an old linguistic joke:

A linguistics professor was lecturing to his class one day. “In English,” he said, “a double negative forms a positive. In some languages though, such as Russian, a double negative is still a negative.

However,” he pointed out, “there is no language wherein a double positive can form a negative.”

A voice from the back of the room piped up, “Yeah, right.”


Then, the joke led me to the solution. Double negative forms a positive! Instead of trying to select accounts that only hold selected Assets, we can exclude Accounts that hold excluded Assets. The remaining Accounts will naturally hold only selected Assets and nothing else. Here is the Set Analysis formula for this solution, that ended up to be fairly compact and elegant:

only({<Account=E({<Asset=E(Asset)>})>}  Account)

Exclude the excluded – double negative had formed a positive!

If you like the level of depth that you see in these articles, please check out the agenda of the Masters Summit for Qlik and find out if it’s right for you. This year, all three summits that we had in Milan, in Johannesburg, and in Austin, were extremely successful. Next year’s locations and dates are still being sorted out, but rumor has it that we might be in Germany in April, in Boston in September-October, and possibly in Brazil in June. Stay tuned!

Leave a Reply

Your email address will not be published. Required fields are marked *