topic Re: Link Table Theory in QlikView

Link Table Theory

Tue, 25 Jun 2013 11:43:56 GMT

I saw that Link tables are include in the Qlikview Best Practices, so I have read various explanations and I thought I would try to create a basic scenario.

I have created the following tables:

Table1:

LOAD * INLINE [

%Key,A,B,%Colour,%Size

1,A1,B1,1,2

2,A2,B2,2,2

3,A3,B3,1,2

];

Table2:

LOAD * INLINE [

%Key,C,D,%Colour,%Size

1,C1,D1,1,1

2,C2,D2,2,1

];

ColourDesc:

LOAD * INLINE [

%Colour, CDescription

1,Red

2,Blue

];

SizeDesc:

LOAD * INLINE [

%Size, SDescription

1,Small

2,Large

];

Then looking at a 'template' to create the link table:

LNK_TABLE:

load distinct

%KEY_FACT1_ID AS %KEY_LNK_ID

, %KEY_DIM1_ID AS %%KEY_DIM1_ID

, %KEY_DIM2_ID AS %%KEY_DIM2_ID

RESIDENT Fct1;

CONCATENATE

load distinct

%KEY_FACT2_ID AS %KEY_LNK_ID

, %KEY_DIM1_ID AS %%KEY_DIM_ID

, %KEY_DIM2_ID AS %%KEY_DIM2_ID

RESIDENT Fct2;

I created the following (amoungst numerous other attempts!):

Link:

Load distinct

%Key,

%Colour,

%Size

Resident Table1;

Concatenate

Load distinct

%Key,

%Colour,

%Size

Resident Table2;

The result is horrible. Can someone explain where I am going wrong, or is it just a bad example?

Thanks

Jason

Re: Link Table Theory

RedSky001 — Tue, 25 Jun 2013 12:05:31 GMT

Try this

Table1:

load *

, %Key & %Colour & %Size as %comb_key

;

LOAD * INLINE [

%Key,A,B,%Colour,%Size

1,A1,B1,1,2

2,A2,B2,2,2

3,A3,B3,1,2

];

Table2:

load *

, %Key & %Colour & %Size as %comb_key

;

LOAD * INLINE [

%Key,C,D,%Colour,%Size

1,C1,D1,1,1

2,C2,D2,2,1

];

ColourDesc:

LOAD * INLINE [

%Colour, CDescription

1,Red

2,Blue

];

SizeDesc:

LOAD * INLINE [

%Size, SDescription

1,Small

2,Large

];

link:

load %comb_key

,%Key

,%Colour

,%Size

Resident Table1;

Concatenate(link)

load %comb_key

,%Key

,%Colour

,%Size

Resident Table2;

DROP FIELDS %Key , %Colour ,%Size FROM Table1;

DROP FIELDS %Key , %Colour ,%Size FROM Table2;

Re: Link Table Theory

Anonymous — Tue, 25 Jun 2013 12:22:48 GMT

Hi,

inorder to create a linked data model follow the below steps:

Table1:

autonumber(x,'table1') as key1,

from tab1.qvd;

Table2:

autonumber(g,'table2') as key2,

from tab2.qvd

Table3:

autonumber(r,'table3') as key3,

from tab3.qvd;

Final:

load

key1,

Resident Table1;

drop fields y,z from Table1;

concatenate

load

key2,

Resident Table2;

drop fields y,z from Table2;

concatenate

load

key3,

Resident Table3;

drop fields y,z from Table3;

Note:

you can see all the tables 1,2,3 combined into one table and its field key1, key2,key3

linked to respective table1 , table2, table3 and each individual tables has distinct dimension fields

and therfore no circular loop will form,

Rgards,

Anant

Re: Link Table Theory

Tue, 25 Jun 2013 13:11:49 GMT

Mark,

Thanks very much. I appear to have missed the point about creating a combined key.

Your result certainly looks alot better than mine!

Regards

Jason