topic Re: Two Fact tables connecting to same dimension tables in QlikView

Two Fact tables connecting to same dimension tables

Tue, 20 Mar 2012 13:02:33 GMT

Hi all,

I have two fact tables which connect to multiple dimmension tables through the same fields, thus forming syntetic keys.

How do i overcome this ?

Please find attachment

Two Fact tables connecting to same dimension tables

Tue, 20 Mar 2012 14:25:08 GMT

for each dimension table create new fields to remove synthetic tables.

The links may contain the same fields, just name them differently for each link

Two Fact tables connecting to same dimension tables

mr_novice — Tue, 20 Mar 2012 15:39:31 GMT

Concatenate the two fact tables to one. You could add a column to see difference between fact rows.

Fact:

LOAD * INLINE [

Sales_ID, Bill_ID, Ship_ID, Product_ID, Customer_ID, Region_ID, Sales_QTY, fType

1,1,1,1,1,1,1,'Sales'

];

Concatenate(Fact)

LOAD * INLINE [

Orders_ID, Bill_ID, Ship_ID, Product_ID, Customer_ID, Region_ID, Orders_QTY, fType

1,1,1,1,1,1,1,'Order'

];

Two Fact tables connecting to same dimension tables

Sun, 06 May 2012 06:57:04 GMT

Hi,

Did you find a solution for this? Concatenating dimension tables into 1 table doesnt seem to be a practical option. wouldnt that mean they will under the same column name?

Re: Two Fact tables connecting to same dimension tables

kaushiknsolanki — Sun, 06 May 2012 08:12:51 GMT

Hi,

Have a look at the attached example.

Hope this will give you clear idea

Regards,

Kaushik Solanki

Two Fact tables connecting to same dimension tables

Sun, 06 May 2012 08:30:08 GMT

I would join the fact tables with the dimension tables, renaming the fields so as to avoid the data being linked - that way you will have two data islands and can re-label your fields in the charts and dropdowns

Example (excuse the odd data)

FactTable1:

LOAD * INLINE [

Meal,PortionID

Big Mac,1

Veggie Delite (Subway),1

Prawn Salad,1,Salad,239

];

Left join

LOAD * INLINE [

Meal,TotFat,TotSugar

Big Mac,24,41

Veggie Delite (Subway),1.9,41

Prawn Salad,14.8,9.3

];

FactTable2:

LOAD * INLINE [

Meal_1,PortionID_1

Brownies,1

];

Left join

LOAD * INLINE [

Meal_1,TotFat_1,TotSugar_1

Brownies,24,41

];

Chris

http://oneqlikatatime.blogspot.co.uk/