AND Mode in list boxes

By default, multiple selections in list boxes are "OR" mode. For most applications this is exactly what we need.

However, there are situations, such as that requested by Wolfgang Praschnig on QlikCommunity this week:
http://community.qlikview.com/forums/p/22224/84898.aspx.

Wolfgang wants to be able to see customers who have bought one product AND bought another product. The default action of QlikView would be to show customers who had bought one product or the other.

To enable "AND mode" in a list box, there are a couple of rules that we need to follow:

1. The field in the list box must be contained in a table that has only 2 fields.

2. The other field in the table is the association (or ID) field. This field is only contained in this table.

3. The table must have been loaded with the "DISTINCT" keyword.

This can sometimes be tricky to achieve, so hopefully an example will help demonstrate.

Lets say that we have 3 tables:

Orders
OrderID, CustomerID, OrderDate

OrderDetails
OrderID, LineNo, ProductID, Quantity, Price

Product
ProductID, ProductName

What we want to do is connect the product name to the Orders table in a way that we can employ AND mode.

This means that we will need to create a table that has the OrderID (to associate to Orders) and a ProductName field (which we will call "ProductNameAND") that we can put in our List Box with AND mode on.

So, first we need to create a table with the OrderID and ProductID from the OrderDetails table:

ProductAND:
LOAD DISTINCT
OrderID, ProductID As PID
RESIDENT
OrderDetails;

Note that I have aliased the ProductID field as PID - this is because I am going to drop it in a minute. Also note the DISTINCT keyword.

Now, bring in the Product name:

LEFT JOIN (ProductAND)
LOAD DISTINCT
ProductID As PID, ProductName As ProductNameAND
RESIDENT
Product;

Finally, drop the PID field so we are left with only the 2 fields as required.

DROP FIELD PID;

Now, we can add the ProductNameAND field in a List Box and the AND mode will be available.

Interesting feature here. When you click on an item in an AND mode box, an "&" will appear to the left of it. Pressing CTRL and clicking on additional "possible" (in white) items will add further "&"s beside those items. However, if you click on an "excluded" item or you click and hold the mouse down for a second on "possible" items, it turns to a "!" instead - i.e. "NOT". This can be really powerful for queries.