QlikView App: Create list boxes that work in an OR mode
In October 2013 the QlikView Masters Summit came to London. During the event there was a Quiz the Masters session and the question was asked was there a way to create ListBoxes that work in a OR fashion rather than an AND.
At the time no one could think of a simple way of achieving this - but I thought I would try a few things out. This document is some of my workings.
The first attempt was based on creating a linked table with all values in for each field that was to be included in the and. These would be split into separate list boxes with Expressions in the List Box. Unfortunately although those selections should not be mutually exclusive - from a data model perspective - they are.
The second attempt kind of worked - but looked pretty bad. The values from each of the fields were placed in a single field with a prefix showing which field they had come from. Selections could then be made from values from each of the source fields and the resultant selections did give an AND of the values. It was a kind of solution.
The third attempt works and the list boxes look exactly like the natural list boxes. This is achieved by loading every value for each field that needs to be used in the OR into a separate table stored as a Data Island. By changing any expressions to use Set Analysis and a P modifier looking at the data islands values can be found for any rows where a selection is made in any of the fields. This ticks the boxes (I believe) for answering the question.
However.... I would really not recommend the third approach as it requires duplication of much of the data model in memory and could perform very badly. Also, if a user was to pick one of the data island fields when they thought they were picking an actual dimension things could go very bad with a Cartesian product occurring. Perhaps a Hide Prefix on those fields would make it less risky though.
So, at the end of the day - this is very much open for discussion and I would welcome any further suggestions on how this could be achieved.