All airlines that have flown to CITY A and CITY B

[#1]

Hi all,

I'm a newbie at Qlikview, and I can't figure a general-purpose way to solve this one kind of problem.

I have a fact table called "flights" with two columns: airline and city.  It shows all flights and their associated originating city and airline.

I have a dimension table called "airlines" and another dimension table called "cities".

I want to enable my users to find all airlines that have (at least once) flow to City A AND flown to City B (not OR), where the user defines the cities of interest.  I have sufficient memory to make as many copies of the "Cities" dimension table as I want.

I would like this to be able to naturally extend to 3 cities, that is, all airlines that have flown at least once to City A AND City B AND City C.

Is there any way to do this without making many copies of my fact table?

Thank you,

Dana Ludwig

UC San Francisco

[Nicole-Smith]

Make a list box with the City field, and check the "AND mode" option.

[Nicole-Smith · Accepted solution]

Also, here's some info from QV help:

AND-Mode in List Boxes

Multiple selections within a field are by default interpreted as logical OR, which means that data associated to any of the selected field values will be shown in the other fields.

It is however possible to set list boxes to AND-mode instead. When multiple selections are made in a list box set to AND-mode, only data associated to all of the selected field values will be shown in the other fields.

Example:

Your data source contains information about which customers have purchased different articles.

When you select a couple of articles in the default mode, QlikView will show you the customers that have purchased any of the selected articles.

When you select a couple of articles in AND-mode, QlikView will show you the customers that have purchased all of the selected articles.

AND-Mode Prerequisites

The mode for the field is set in List Box Properties: General tab.

When And mode is activated, an ampersand ("&") will be displayed in front of selected values.

If clicking and holding a value for a moment, the selection will switch from (AND) selected (green) to NOT selected (red). The ampersand will also be replaced by an exclamation mark ("!"). The NOT selection which is a forced exclusion of the value or values so marked, can only be made when a list box is in And mode.

AND-Mode criteria

A field cannot always be set to logical AND-mode. The reason for this is that the and alternative is logically meaningful only if the concerned field is linked to only one other field. The following criteria must be fulfilled:

• the field must only exist in one logical table,
• the field must be the second column of no more than two columns,
• the table must not contain any duplicate records, and
• the field must be loaded with the distinct qualifier. If the table is loaded using a select statement, you must use a preceding load distinct *.

Setting a List Box in AND-Mode

The following procedure describes how to set a list box in AND-mode instead of the default mode (logical OR). Perform the following steps:

1. Make sure that the AND-mode criteria are fulfilled.
2. Right-click on the list box and select Properties.
3. On the General tab, mark the And mode check box.

[#4]

Thank you, Nicole!  I found more details on this page:

http://community.qlik.com/message/260969#260969