And-Mode: How to enable AND and NOT selections with Green, White, Grey...and Red!

[harvinderjohal]

Learn how to utilize And-Mode within your own QlikView application.  As described in the QlikView online help, by default, when making multiple selections within a field or list box, the selections that you make are interpreted as logical OR, which means that data associated to any of the selected field values will be shown in other fields.

For example, if you have two fields or list boxes such as Customers and Products, and your question is ‘Show me all customers that bought ProductA and ProductB’, QlikView, by default, will show all Customers that bought ProductA OR ProductB OR Both such as in the example below.  Notice that selecting both ‘Adihash Running Shoe’ and ‘Aino Shoes’ from ProductName list box is returning all Customers that bought either product or both.

This is a valid return, however, you may say ‘Actually, I want to see a list of Customers that bought BOTH ProductA AND ProductB, not one or the other’; this is where you can leverage And-Mode to get the results you want.

Notice the field andProductName above, configured to utilize And-Mode - showing an ‘&’ in front of values selected, and the results of the Customers field that bought both ‘Adihash Running Shoe’ and ‘Aino Shoes’.  The list of Customers returned is much smaller than in the first example.

You are already familiar with QlikView’s associated model, meaning that any values you select will be highlighted in the color Green, associated values with your selection will be left in White, and anything not associated will be turned to Grey.  And-Mode allows for a forth color – Red.  By clicking onto a value in the And-Mode field, and holding the mouse/CTRL button, the selected value will turn from Green to Red and will be preceded by a ‘!’ mark vs. a ‘&’ symbol.  This operation is effectively a NOT operation.  Your question may now be, ‘Show me all Customers that bought ProductA AND bought ProductB but did NOT buy ProductC’ such as in the example below.

To enable And-Mode, there are rules that you must follow in order to leverage this function. Please refer to the Online Help in QlikView desktop for more information on requirements, however, from a high-level view, you need to know the question you want to perform the AND operation on – such as the examples we have been using so far:  'I want to see all Customers that bought specific Products together'.

In order to enable this, you need to create a special table in QlikView that has 2 and only 2 fields.  The first field is the field you will be linking on to your data, such as Customers. Since we want to find all Customers that bought specific products, we need to have Customers in our And-Mode table.
The second field must be the field you want to perform the AND operation on, in our case, it is Products.  This field must not link to any field in your datastructure – in the example above, we had aliased the field as andProductName vs. ProductName.

In the video below, I had created a QlikView application that was bringing in data from 5 tables: Customers, Order_Header, Order_Details, Products, Categories, and Shippers.

In order to create my And-Mode table, I had to create a temporary join table called andModeJoin, that I later dropped, that would link from Customer to Order_Header to Order_Details to Products so that I could have related values of Customer and Products.  I then created my And-Mode table to store the results of the andModeJoin temporary table and later dropped the temporary table.  This is how I did it in my Script – note that when creating your And-Mode table, you must use the Distinct qualifier when Loading fields:

andModeJoin:
Load CustomerID, Customer Resident Customers;
Join (andModeJoin)LOAD OrderID, CustomerID Resident Order_Header;
Join (andModeJoin)LOAD ProductID, OrderID Resident Order_Details;
Join (andModeJoin)LOAD ProductID, ProductName Resident Products;



andMode:
LOAD Distinct
       Customer,
       ProductName as andProductName
Resident andModeJoin;
DROP Table andModeJoin;

Once the And-Mode table is created, you need to add your AND field your QlikView document and turn on the option ‘And Mode’ in properties.

Please refer to the following YouTube video:  http://www.youtube.com/watch?v=Wg5Jgnki7yE

[pennetzdorfer]

Yes! That's exactly what I've been looking for ... thx for that really useful article!

Just one thing: is there a way to get the following result?

Show me all customers who bought the "Basket vest" and ANY "*Shoes*" (wildcard search).

Regards,

Florian

[#3]

Hi Florian, regarding:

and ANY "*Shoes*" (wildcard search).

My question is similar.  My "Products" are disease names, and there are often a bunch of names that mean nearly the same thing (and ANY "*Diabetes Mellitus*").  The only thing I can think of that might work is to create another table called "PRODUCT_GROUP" that relates products to product group names.  Then you could extend his "andModeJoin" one more level to PRODUCT_GROUP_NAME.  The problem with this is it is pretty static.  I would like my users to be able to build these "ANY" groups on the fly, as you did, with "*Shoes*".

Harvey, any thoughts?

Dana Ludwig, UCSF

[vgutkovsky]

Harvey, that's a neat idea! Just a thought, but another way to achieve this result is to create an "AND" alternate state and a "NOT" alternate state. You can then catch values selected in list boxes in these states, build set analysis in variables (using set multiplication to achieve the ANDs and set subtraction to achieve the NOTs). That would achieve the same thing without any DM mods, no?

Regards,

Vlad

[harvinderjohal]

Unfortunately the grouping (the business logic) would need to be done in the data model, in the script, in order to support this vs. on-the-fly by the users consuming the app.  It's a design thing.

[harvinderjohal]

I think with Alternate States (Basket Analysis really), would still comprise an issue of either an AND or an OR among selections.

Using a retail data example, lets say for the 'Alternate State 1', an AND state, you selected 2 values of a product that customers could have bought.  What you are saying is show me ALL my customers that either bought product 1 and/or bought product 2 or bought both products. 

Now lets say for the 'Alternate State 2', a NOT AND state, you selected 1 value of a product, product 3 that not part of the first list of products, that customers did not buy.  You would be asking 'Show me all customers that either bought product 1 and/or bought product 2 or bought both but did not buy product 3.  This would still not be a forced AND operation.  If you want to ask 'Show me all customers that bought product 1 AND product 2 together (not either separately) and also did not buy product 3', then you would need to follow the above data modeling example for forced AND operation.

[vgutkovsky]

Not quite what I meant. I will post an example tomorrow of how this can be done.

[vgutkovsky]

Harvey, take a look at the attached--that's how you could accomplish AND/OR stuff with alternate states. One advantage of your approach over mine is that your ANDs are linked to the data model so selections reflect in list boxes. My approach is primarily meant for charts. On the other hand, one advantage of mine over yours is that wildcard searching in either list box is possible. And no DM changes. Just a thought. Both approaches have their merits IMO.

Cheers,

Vlad

[#9]

Thank you so much for your post Vlad !

Your exemple is simply perfect.

I used And-Mode method for 3 months but in the end yours is widely better !

Best regards,

David

[vgutkovsky]

In case anyone is interested in a more detailed and polished explanation of the solution I posted above, I've written up a little article which you can find here: "AND" and "NOT" List Boxes in QlikView – Infinity Insight Blog

Cheers,

Vlad