topic Re: Regarding Link Tables between 2 fact tables in QlikView

Regarding Link Tables between 2 fact tables

pauljohansson — Wed, 06 Feb 2013 16:18:15 GMT

Hi,

When you read about how to create a link table between 2 fact tables i have on several places(for example in book "Qlikview Developer 11" page 289) been told that i should identify the common fields between my fact tables and create one %Key field.

But, lets say that i use the respective %Primary Key field from each fact table. Why should this not be possibly, or am i missing out something here? Do I always need to use the same %Key field for my two seperate fact tables?

Attached is a data model where i use the fact table respective primary key.

Comments and thoughts are welcome,

Paul

Re: Regarding Link Tables between 2 fact tables

Thu, 07 Feb 2013 10:58:36 GMT

"I should identify the common fields between my fact tables"

it means if your tables contain more than one common fields (if so Qlikview automaticly create synthetic kays) You should identify them to create link table

as i look at your data structure I think You don't need to create link table

Could You send data structure before creating LInkTable? I need to see if there are any synthetic keys...

Re: Regarding Link Tables between 2 fact tables

Jason_Michaelides — Thu, 07 Feb 2013 11:03:40 GMT

I don't think you need a link table here. I would concatenate the two fact tables and create a FactType field I think.

Re: Regarding Link Tables between 2 fact tables

pauljohansson — Thu, 07 Feb 2013 12:21:42 GMT

Pari Pari,

What i have done is this

1)I have created a primary key for each of the fact tables:

t_FactSalesPgMonthly:

LOAD
AutoNumber(CustomerKey & AgreementKey & ProductGroupKey & TimeKey & BusinessModelKey & IsWalletRelated & MainStatisticsGroupKey & PartyOfAgreementKey & CountryKey & CustomerGroupKey & CustomerStatisticsGroupKey & SalesDistrictKey) as FactSalesPgMonthlyKey,
...

t_FactWalletMonthly:
LOAD
AutoNumber(AgreementKey & ProductGroupKey & TimeKey & BusinessModelKey & MainStatisticsGroupKey & PartyOfAgreementKey & CountryKey) as FactWalletMonthlyKey,

...

as you can see, there are many fields (but not all) that match between the fact tables.

2) I have then created a Link Table consisting of these two Primary Keys and selected dimensions from the fact tables.

So, my questions is: When i read about Link tables, all examples ive seen create a common Key Link field with only the common fields between the two fact tables. As you can see in my data model, the respective fact table use its own field to link in via the link table.

I attached the .qvw where i create the link table (ve limited the data så dont bother with that diagrams looks strange)

Paul

Re: Regarding Link Tables between 2 fact tables

pauljohansson — Thu, 07 Feb 2013 12:28:26 GMT

Hi Jason,

Note that i have created the "Primary Key" for each of the fact table myself. There are several (but not all) key fields that are similiar in each of the fact table.

Do you know an extensive description that explains all there is to know about Link tables?

Paul

Re: Regarding Link Tables between 2 fact tables

Thu, 07 Feb 2013 12:28:33 GMT

i cann't open Your file becouse I temporary lost my QV licence key:P

what i want You to do is straight load all Your tables (just first 100 load * from...)

and post here Table Structure printscreen

Re: Regarding Link Tables between 2 fact tables

Jason_Michaelides — Thu, 07 Feb 2013 12:34:52 GMT

If you use CONCATENATE then the two tables don't need to be exactly the same.

Re: Regarding Link Tables between 2 fact tables

pauljohansson — Thu, 07 Feb 2013 12:41:20 GMT

Hi Jason,

Yes i know.

What im trying to pin point is if the field that is used to link into the link table must the same field for both of the fact tables.

If you look at my structure you can see that I use the respective Primary Key(created by me) to link into the LinkTable for each of the fact table.

Paul

Re: Regarding Link Tables between 2 fact tables

pauljohansson — Thu, 07 Feb 2013 12:45:10 GMT

Pari Pari,

Im not sure i understand what you mean with "straight Load" anyway, ive have limited load to 100 rows and exported 3 xls representing the tables of interest.

The data model is in the first post,

Paul

Re: Regarding Link Tables between 2 fact tables

Jason_Michaelides — Thu, 07 Feb 2013 12:52:58 GMT

Simple answer is no. Both fact tables can link into the link table with different keys but you will obviously need an internal relationship in the link table..

Re: Regarding Link Tables between 2 fact tables

pauljohansson — Thu, 07 Feb 2013 13:31:46 GMT

Yes, without internal relationships in the LinkTable there wont be much of linking

My thinking goes like this:

if i keep all my dimension fields in the link table, i need to make sure that my fact table is linked to the link table with the "primary key". Otherwise i might risk that some of my facts wont be "accessible" (since I with all my dimensions in the LinkTable always "start" my analysis from there).