Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harvinderjohal
Creator
Creator

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

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.


figure1.png

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.

figure2.png

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.

figure3.png

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.

figure4.png

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.

figure5.png

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.

figure6.png

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

12 Replies
cheburashka
Creator III
Creator III

Hello Harvey,

Can you please tag your post?

It only showed up as the fifth post on my search and it is clearly the one I was looking for.

,KR Koen

Anonymous
Not applicable

Similar to Henric's post? AND and OR

And Henric mentioned Jennell's post AND-Mode

(Just crosslinking here)    

Anonymous
Not applicable

This is great. Have a question though?

Is there anyway we can also chose a order date or a order date range and do the AndMode for that particular date or date range? Will it work?

Thanks.