topic Re: how do i build my link table data model with multiple combination of keys? in QlikView

how do i build my link table data model with multiple combination of keys?

supriyabiware — Mon, 25 Sep 2017 07:53:03 GMT

Hello All,

I have 4 facts and 3 dimensions in my data model

Facts - 1. SALES (Keys - Product ID, Supplier ID, Plant ID)

2. PRODUCT (Keys - Product ID)

3. REDUCTION YEAR (Keys - Product ID , Supplier ID)

4. ENQUIRY (Keys - Product ID, Supplier ID)

Dimensions - 1. PRODUCT (Key - Product ID)

2. PLANT (Key - Plant ID)

3. SUPPLIER (Key - Supplier ID)

I tried linking FACTS using JOINS but somehow the results dint turn out to be fine. (Duplicates issue i think)

I cannot concatenate facts since they are huge and there are no common fields other than IDs

Now i am trying using LINK TABLE but am confused how can use the keys here. all the keys are necessary to be in LINK TABLE since i need information from all tables under one objects.

Thanks

Supriya.

Re: how do i build my link table data model with multiple combination of keys?

supriyabiware — Wed, 27 Sep 2017 07:10:27 GMT

Hello All,

Can someone tell me the best way to model this dataset?

Facts - 1. SALES (Keys - Product ID, Supplier ID, Plant ID)

2. REDUCTION YEAR (Keys - Product ID , Supplier ID)

3. ENQUIRY (Keys - Product ID, Supplier ID)

4. PRODUCT (Keys - Product ID)

Dimensions - 1. PRODUCT (Key - Product ID)

2. PLANT (Key - Plant ID)

3. SUPPLIER (Key - Supplier ID)

Thanks

Supriya

Re: how do i build my link table data model with multiple combination of keys?

Kushal_Chawda — Wed, 27 Sep 2017 07:43:52 GMT

Sales:

LOAD *,

autonumber( [Product ID]&'|'&[Supplier ID]&'|'&[Plant ID]) as Key_Sales

FROM SALES;

REDUCTIONYEAR:

noconcatenate

LOAD *,

autonumber( [Product ID]&'|'&[Supplier ID]) as Key_ProductYear

FROM REDUCTIONYEAR;

ENQUIRY:

noconcatenate

LOAD *,

autonumber( [Product ID]&'|'&[Supplier ID]) as Key_Enquiry

FROM ENQUIRY;

PRODUCT:

noconcatenate

LOAD *

FROM PRODUCT;

LinTable_Temp:

LOAD distinct Key_Sales,

[Product ID],

[Supplier ID],

[Plant ID]

Resident SALES;

drop fields [Product ID],[Supplier ID], [Plant ID] from SALES;

concatenate(LinTable_Temp)

LOAD distinct Key_ProductYear,

[Product ID],

[Supplier ID]

Resident REDUCTIONYEAR;

drop fields [Product ID],[Supplier ID] from REDUCTIONYEAR;

concatenate(LinTable_Temp)

LOAD distinct Key_Enquiry,

[Product ID],

[Supplier ID]

Resident ENQUIRY;

drop fields [Product ID],[Supplier ID] from ENQUIRY;

concatenate(LinTable_Temp)

LOAD distinct

[Product ID]

Resident PRODUCT;

LinkTable:

noconcatenate

LOAD *

resident LinTable_Temp;

drop table LinTable_Temp;

Now you can load the dimension table as it is

Re: how do i build my link table data model with multiple combination of keys?

supriyabiware — Thu, 28 Sep 2017 06:46:49 GMT

Thankyou Kushal for your reply.

Somehow i am not able to link all the three facts using this LINK TABLE. when i select Product ID it should link me to respective PLANT ID for that Product. instead there is nothing.

Can i use Outer Join in LINK TABLE instead of concatenate? what do you think on this?

Also when i load dimensions as it is - and take dimensions and facts objects in straight table its taking lot of time to load like 10-15 mins.(Data from all facts would be 50 million rows approx)

Thanks

Supriya