in filter, display the pre-mapped values, but filter by mapped-value 'behind the scene'

[ynottableau]

I have a map below, which related food to fcode. fcode is the key that links multiple tables together.

map1:

[food,fcode

apple,a

apple-pie,a

banana,b

banana-float,b

cherry,c

cherry-coke,c];

Let's say

Table 1 -uses fruits (applymap() can be used to turn food into fcode)

Table 2 -uses fcode (can not reverse into food)

Table 3 -uses fcode (can not reverse into food)

How can i create a filter, such that:

1. the filter display the food values. (i.e apple-pie and not 'a')

2. When selecting food values, the filter is applied to all tables (1,2,3). I.e if I select apple-pie (or apple), the filter also set  fcode to 'a' and table 2,3 will display only information relating to 'a'. Note that it is accepted for either apple-pie or apple to be selected to filter for fcode 'a'

If I use applymap() on table 1, and filter by fcode, i lose context for my users who only understands food.

[Vegar]

You could try something like this:

A:

Load

Fruit,

Applymap ('map1', Fruit, 'missing') as fcode,

...

From sourceA;

B:

Load 

 fcode,

...

From sourceB;

C:

Load 

fcode,

...

From sourceC;

[ynottableau]

Thanks for the reply.

The small problem with what you prescribed is that 'apple' and 'apple-pie' must both be selected to filter for all fcode 'c'

If a user select only 'apple' it will not show the correct values in the other tables.

If possible, I need it so that even if only 'apple' is selected, the other tables show all values for fcode 'c' (as if the user had selected both 'apple' and 'apple-pie')

[marcus_sommer]

If you link all three tables per fcode you get exactly what you are describing as target - not as direct selections in the field fcode but the associations between the tables will behave in this way.

If your views don't show the expected results it means that your link isn't suitable for this target or something else is wrong. Personally I doubt that your approach is really expedient because it's only a connection of fruit-categories. Any further relations to periods or stores/countries and so on are not included and couldn't therefore considered. I think you will need some more adjustments to the data-model.

[ynottableau]

food and fcode are being used here as masks (and also for the sake of simplification) for the actual data that I am displaying. 

There are other synthetic keys (5 to be exact). The approach described works for my data-model but only when the user select both 'apple' and 'apple-pie'

To have a clean 1 field filter is more for user friendliness sake. My work around is just to put both fcode,fruits into a table to display for the users to select and filter.

Appreciate the responses. Thanks all.

[marcus_sommer]

IMO you have not a suitable data-model. I suggest to consider a synthetic key always as an error which leads to an invalide data-model - even if it seemed to work and native features like the "standard" intervalmatch results in one. The last should be rather regarded as an exception and not that's a general working approach.

And the trouble increased if there isn't a single synthetic key else n ones which may even create synthetic keys between them.

Beside this the official recommended data-model is a star-scheme which means having a single fact-table with n surrounding dimension-tables because it's the best compromise between development efforts, performance and usability. Therefore I suggest to rethink your data-model again.