Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
venkat_94b4
Contributor

Link Table

How to combine two fact tables with synthetic keys using link table? Can any one help me with one good example...

Thanks in Advance....

9 Replies
Highlighted
Siva_Sankar
Honored Contributor

Re: Link Table

A key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues.  It can also help to tidy  up a schema that has tables all over the place.  The goal is to create a star (and sometimes snowflake) schema with dimension tables connected to a central key table.  Unlike in classical data warehousing, the central table doesn't often have the measures - they generally stay in the dimension tables.

There are 3 rules for creating a key table.  The first 2 are very straightforward - the last is where you need to use your skill and judgement to create the right result.  So, here are the rules:

1.  All tables should have a primary key.  If a table doesn't have one unique key, derive one using a function like autonumber or autonumberhash256.

2.  Break all the existing joins by renaming foreign keys (e.g. in the Orders table, rename CustomerID to O_CustomerID).  For a "pure" star schema, there should be no QlikView Associations remaining and all the tables should be standalone.  From a pragmatic point of view, it is fine to leave some hierarchical tables associated (e.g. Product to ProductCategory) to have a more "snowflake" schema.

3.  Use a mixture of Concatenate and Join to generate the Key table using the Resident data.  You will load the previously renamed foreign key with the correct name so that they connect to the right dimension table (e.g. ... O_CustomerID As CustomerID).


Find the attached example.


Regards,

Siva

Highlighted
adiarnon
Contributor III

Re: Link Table

can you giva the fact tables so we try to help you to combine them?

Highlighted
venkat_94b4
Contributor

Re: Link Table

qvw file will not open in my edition...could u please send me the code here or in word file....

Highlighted
venkat_94b4
Contributor

Re: Link Table

Hi adi,

thank u...i dnt hav tables,just i wish to know how to do with one example....

Highlighted
MVP & Luminary
MVP & Luminary

Re: Link Table

Hi Venkat,

Try sample script like below

Sales:

LOAD

A & '_' & B & '_' & C AS Key,

A,

B,

C,

Sales

FROM Table1;

LinkTable:

LOAD DISTINCT

A & '_' & B & '_' & C AS Key,

A,

B,

C

RESIDENT Sales;

DROP FIELDS A, B, C From Sales;

Budget:

LOAD

A & '_' & B & '_' & C AS Key,

A,

B,

C,

Budget

FROM Table2;

Concatenate(LinkTable)

LOAD DISTINCT

A & '_' & B & '_' & C AS Key,

A,

B,

C

RESIDENT Budget;

DROP FIELDS A, B, C From Budget;

Hope this helps you.

Regards,

Jagan.

Highlighted
venkat_94b4
Contributor

Re: Link Table

Thank u jagan..... and how can i distinguish the data between Sales and Budget data?? Sorry if my question is wrong.... i wish to know how can i get the data of  Sales and Budget after Link the tables....

Highlighted
Not applicable

Re: Link Table

Hi,

First, You have to drop the synthetic table from the model. I advise you to do that with AutoHashNumber's function and next, you have to drop this fields from one of tables. After that you can join or concatenate tables. Maybe in this situation you'll have clear model for work and you'll keep all fields.

Simplest way is to rename the fields from synthetic table, and keep just one field to connect between tables.

Regards,

Venelin

Highlighted
MVP & Luminary
MVP & Luminary

Re: Link Table

Hi Venkat,

If you want to get the Sales, then use Sales dimension, budget is separate field, or else you can add a flag.

Sales:

LOAD

A & '_' & B & '_' & C AS Key,

A,

B,

C,

Sales

FROM Table1;

LinkTable:

LOAD DISTINCT

A & '_' & B & '_' & C AS Key,

A,

B,

C,

'Sales' AS Flag

RESIDENT Sales;

DROP FIELDS A, B, C From Sales;

Budget:

LOAD

A & '_' & B & '_' & C AS Key,

A,

B,

C,

Budget

FROM Table2;

Concatenate(LinkTable)

LOAD DISTINCT

A & '_' & B & '_' & C AS Key,

A,

B,

C,

'Budget' AS Flag

RESIDENT Budget;

DROP FIELDS A, B, C From Budget;

Hope this helps you.

Regards,

Jagan.

Highlighted
Not applicable

Re: Link Table

Hi,

what if we had a third table with just one common key A and other uncommon foreigns Keys (W,X).

Should we create a unique key for this table with all foreigns Keys? or we just with A? or we don't have to create a unqie one?