topic multiple fact tables and different selections in QlikView

multiple fact tables and different selections

Thu, 11 Mar 2010 12:22:26 GMT

Hi,

I hope someone can help me with the following issue. I have two fact tables. They share a lot of the same fields. I tried to concatenate the tables, but that makes my data corrupt. I tried linking them together but if I make the keyfields the same, Qlikvieuw makes a linking table with synthetic keys. So I renamed the keyfields in the Boekingen table so that there is no problem. My data is getting loaded correct now. However I now have the problem that in the different tabs, you need to make new selections. For example: %KeyGroep in the facttable is the same as %Keygroepnaamfin in the boekingen table. How can I make Qlikview understand that if I select %KeyGroep in Sheet 1 he needs to connect that with %KeyGroepnaamfin in sheet 2?

Thanks, Yvonne

multiple fact tables and different selections

Thu, 11 Mar 2010 13:03:29 GMT

I have the same problem. I tried to create a "Master Dimension" table with the join of all dimension fields, creating a new unique key through autonumber function. This key must be generated in the fact tables. This way you can create your own "syntetic table".

The problem comes when you have so much dimension fields or you try to filter by a field that doesn't exist in "Master Dimension" table.

I need a better solution too...

multiple fact tables and different selections

Thu, 11 Mar 2010 15:24:26 GMT

Hi iefje

You probably need to use a solution that uses a link table which links common fields between your two fact tables. There are good posts about link tables in this forum.

One of the things you should do is not to rename your fields in the Boekingen table so that you can see which fields are common to both fact tables.

1. To link the fact tables and the calendar, you can create a combined key out of the shared common fields. For example, if the tables share the same key number, date and product number, create a key out of all three fields ie key & date & productnumber. To help performance, you could also use the Autonumber function to generate numbered keys out of a complex key ie. Autonumber([Combined Key])

2. For each fact table: create your complex key

3. Create your link table by loading DISTINCT the complex keys from the two fact tables & calendar and the common fields.

4. Drop the common fields from both fact tables. You now have one single Link Table which now linked to the fact tables and calendar.

I hope that puts you on the right track. Again, check out Link Tables in this forum for good nuggets of information.

multiple fact tables and different selections

Thu, 11 Mar 2010 15:53:35 GMT

Hi Jeanne, thanks again for your help and advise.

I will read some more about link tables and then will try to do what you suggested.

Thanks, Yvonne

multiple fact tables and different selections

wizardo — Thu, 11 Mar 2010 17:55:02 GMT

hi Jeanne, (i posted a reply once but then i noticed it didnt show so i am posting again.

plz dont think im spaming your mailbox

to the point.

fcrespo wrote:

The problem comes when you have ............. or you try to filter by a field that doesn't exist in "Master Dimension" table.

this is one of the main issues i have with link tables

i use them a lot and they work perfectly if you can cover the entire range of values. but if there are missing keyValues from some of the tables then it starts to get messy.

so far i haven't find a satisfactory method of fixing this in link table paradigm

i guess it can either be solved via joining some KeyValues along the way and not only concatenating them

or maybe by adding null containing records for all the keyValues that are missing

but neither methods have produced good results

maybe someone can shade more light:)

Mansyno