Data normalization / Searching across multiple fields

Question

have some denormalized data, along the lines of the following:

FruitData:
LOAD * INLINE [
ID,ColumnA, ColumnB, ColumnC
1,'Apple','Pear','Banana'
2,'Banana','Mango','Strawberry'
3,'Pear','Strawberry','Kiwi'
];

MasterFruits
LOAD * INLINE [
Fruitname
'Apple'
'Banana'
'Pear'
'Mango'
'Kiwi'
'Strawberry'
'Papaya'
]; 

And what I need to do is compare these fields to a master list of fruit (held in another table). This would mean that if I chose Banana, IDs 1 and 2 would come up and if I chose Strawberry, IDs 2 and 3 would come up.

Is there any way I can create a listbox that searches across all 3 fields at once?

Answer 1

A list box is just a mechanism to allow you to "select" a value in a certain field as a filter. The real magic behind what Qlikview is doing comes from the associations made in the data model. Since your tables have no common field you couldn't, for example, load a List Box for Fruitname and click something and have it alter List Boxes for other fields such as ColumnA, B, or C. To get the behavior you want you need to associate the two tables. This is can be accomplished by concatenating the various columns into one column (essentially normalizing the data).

[LinkTable]:
LOAD Distinct ColumnA as Fruitname,
              ID
Resident FruitData;

Concatenate([LinkTable])
LOAD Distinct ColumnB as Fruitname,
              ID
Resident FruitData;

Concatenate([LinkTable])
LOAD Distinct ColumnC as Fruitname,
              ID
Resident FruitData;

You can see the table this produces here:

and the data model looks like this:

and finally, the desired behavior: