Apologies for the confusing discussion title, but I'm not sure how to explain what I need!
Basically, I have a field named 'Groceries', with the following values:
- Apples
- Oranges
- Bread
- Cereal
- Water
- Coffee
I would like to create a new field, named 'Type', as follows:
If(Wildmatch(Groceries,'Apples', 'Oranges', 'Bread', 'Cereal'), 'Food', If(Wildmatch(Groceries,'Water', 'Coffee'), 'Drink')) as Type,
which works fine.
However, within 'Type', I would like a third category called 'Fruit', which includes 'Apples' and 'Oranges'. This doesn't work as QV doesn't seem to like a value being assigned to two different categories within one field.
It should also be possible to keep the Dim table and calculate Sum(Total) per Type in that table and also count(ShopID) per Type using an intermediate joined table to create the count.
Just to prevent row duplication in your fact table (and besided this,your average cost calculation seems to be something that is more referring to Type than the facts). If you don't bother duplicating rows in your original table, then you can go with joining Dim to Facts.
But in total, it's hard to recommend which way to go unless knowing your total requirements and constraints, and this will be more than should be discussed in this thread.
