Link Table Theory

[#1]

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

[RedSky001 · Accepted solution]

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;

[Anonymous]

Hi,

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

Table1:

autonumber(x,'table1') as key1,

y,

z,

t,

y

from tab1.qvd;

Table2:

autonumber(g,'table2') as  key2,

y,

z

m,

n

from tab2.qvd

Table3:

autonumber(r,'table3') as   key3,

y,

z,

f

from tab3.qvd;

Final:

load

key1,

y,

z

Resident Table1;

drop fields y,z from Table1;

concatenate

load

key2,

y,

z

Resident Table2;

drop fields y,z from Table2;

concatenate

load

key3,

y,

z,

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

[#4]

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