AND and OR

[hic]

In the Qlik engine, the logic of the selections is always an OR between selections in the same field, and an AND between selections in different fields. Selecting e.g. two products and one customer is very much like the WHERE clause in the following SELECT statement:

     SELECT … WHERE (Product='Cap' OR Product ='Tracksuit') AND Customer='ACME' ;

Under some special circumstances, you can however use something called AND-mode. Read more about it in Jennell’s excellent blog post: AND-Mode. With AND-mode you can select two different products and find the customers that bought both.

However, the AND-mode logic is quite different from a standard AND operator in a WHERE clause: And it does not work at all the same way as OR-logic. There are theoretical implications that do not exist for OR logic.

For example: If you select two products and demand an OR between them, the possible values of all other fields are immediately determined: Any field value implied by either of the products is marked as “possible”.

But if you instead demand an AND between them, it is not clear what you mean: Do you mean “Customers” that have bought both products, or do you mean “Months” when both products have been sold? Or do you mean “Countries” where both products have been sold? Just specifying the two products is not enough to determine a result. You also need to specify the field that the AND-mode refers to.

The example shows that the AND-mode demands an intermediate iterator: The AND-mode always infers a second field for which the AND-logic is relevant. This is a theoretical problem that has nothing to do with how the logic is implemented in the software.

Let’s look at SQL: In a standard SELECT statement, the conditions on either side of the AND operator almost always concern two different fields. It would not make sense to demand

     SELECT … WHERE Product='Cap' AND Product ='Tracksuit' ;

since there are no individual records that fulfill that requirement: "Product" can only have one value at the time. But this is exactly the type of requirement that you have in AND-mode - but operating on a group of records instead of on a single record.

If you would implement something similar to AND-mode in SQL, you would need to join a table with a copy of itself. The following will pick out customers that have bought both a Cap and a Tracksuit:

     SELECT DISTINCT Customer FROM Orders AS Orders1

     INNER JOIN Orders AS Orders2 ON Orders1.Customer=Orders2.Customer

     WHERE Orders1.Product='Cap' AND Orders2.Product='Tracksuit'

Again, an intermediate iterator is needed: Here it is "Customer" - the field used to join the two tables.

In QlikView we have chosen to solve this problem by demanding a two-column table for AND-mode, where the first column defines the iterator (e.g. Customer), and the second is the field where the user makes the AND selection (e.g. Product).

So, the two-column table is not just an arbitrary limitation; it is instead a framework implied by the theoretical problem.

HIC