Regarding Link Tables between 2 fact tables

[pauljohansson]

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,

br

Paul

[#2]

"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...

[Jason_Michaelides]

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

[pauljohansson]

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)

br

Paul

[pauljohansson]

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?

br

Paul

[#6]

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

[Jason_Michaelides]

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

[pauljohansson]

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.

br

Paul

[pauljohansson]

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,

br

Paul

[Jason_Michaelides]

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..