By design, QlikView uses OR condition when multiple values are selected in a List object. That means when a user select more than one value in a List, QlikView will execute an OR operation, associating values from associative model from any value selected from List. However, sometimes the user needs to evaluate an AND condition. For example, from a Movies Database, who are the actors has worked together? Of course if a user select two or more actors from the List, QlikView will evaluate this selection as an OR operation. The challenge is change this behavior. So, this post is designed to teach you how to change load script to turn on this option from a List object. If any List object has its properties accessed, from General tab is possible to see an option to turn on the AND MODE. But, this option is disabled and can not be selected if some procedures aren't executed at script level.
To test this procedure just import data from Movies dashboard, a file installed as a sample with QlikView Desktop. To use data from this document just execute the command
Binary as shown on the following example. Of course you need check if the file is present in the path indicated.
Binary command ins't necessary to use AND MODE and this post will use it just to load some data as sample. Pay attention to insert
Binary in first line of script.
Binary [c:\program files\qlikview\examples\documents\movies database.qvw];
Changing Data Model to Use AND MODE
To use AND MODE in a List, data from different tables needs to be loaded as unique values. For example, we know that an actor maybe has worked in two or more movies. And a movie has typically many actors. To enable AND MODE on a List is necessary create a table using actors and movies as unique pairs. For that, the
DISTINCT instruction will be used at script level. This will allow create a new table with IDs from both actors and movies, like image at side.
Remember that by default when an actor is selected, all other values are not associated. Selected value is shown using green color while associative values from other lists keeps white. What we want is keep white in the same list, all actors that has worked together, that means, on same movie. Even if an actor is selected, when AND MODE is turned on, all actors with common movie will keeping white color. To enable this behavior is necessary create a new table with values from actors and movies. But, in this new table only distinct pairs of values should be inserted.
Using this concept, go to script editor pressing
CTRL + E shortcut. In last line (empty one) use the following instruction to create a new table named ANDActor. Don't worry about how to load actors or movies data, because binary statement will load all tables before you use it. The
Load Distinct statement will create a new table using distinct combinations from both fields (FilmID and Actor). The
Resident instruction refers to load data from a previous table loaded before. It's like a FROM statement in a SQL command.
If you open the Table Viewer before write the statement below, you will be able to see original tables loaded by
Binary command. The actor table has two fields, actor and film. If the same actor has worked in different movies, his or her name will be replicated.
Actor As ANDActor
As a result a new field named ANDActor is now available. Let's go to add lists to main interface of QlikView document. Title has movies names. Check the next section how to configure Lists to use AND MODE
Setup Lists for AND MODE
Now your model is ready to use AND MODE and can be configured accessing List properties clicking with right button over
ANDActor List object. Basic on distinct values loaded in the new table, now it's possible select AND MODE from General tab. Remember that only this field is available to enable AND MODE functionality because only this field was configured with unique pair of values. If you try change this same feature using another list, this option will be disabled and couldn't be selected.
To test this behavior, select a actor from the list. Behold that other values in the list will remain white. Why? Because any white values mean that actors had worked together. For example, if you select Harrison Ford from ANDActor list, a lot of white rows keeps showing. All of them worked together in one or more movies. But, scrolling down is possible to see actors names in gray, since some of them never worked with Harrison Ford. More a thing different can be noticed: The ampersand signal is shown.
When selecting some titles from films list is possible to see all actors that had worked together on this movie. That's a normal procedure. But, after clear all selections try to select Harrison Ford and Bradley Gregg. Use CTRL key to select both of then. Is possible to see that these two actors worked together in Indiana Jones and the Last Crusade. But, not only this. All other names in white color from actors list worked together. More one resource is available while using AND MODE. It's a NOT mode.
Another way to use AND MODE is clicking over a value in the List and hold mouse button up to cell color changes to strong red. For example, the user would like to know movies where Harrison Ford worked but Angela Staines not. First, clear all selections and after that click on Harrison Ford name. Hold CTRL key while clicking over Angela Staines name. Keep mouse button down up to change cell colors and release button followed by CTRL key. Results should be something like picture below. So, answer for user is a list of star wars movies.
In the other hand, selecting both Harrison Ford and Angela Staines while not using NOT procedure, will show films where both actors worked together. In this case, both names are painted in green. In the Title list is possible to check other movies.