Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Link Table Theory

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

1 Solution

Accepted Solutions
msheraton
Contributor III

Re: Link Table Theory

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;

3 Replies
msheraton
Contributor III

Re: Link Table Theory

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;

anantmaxx
Contributor III

Re: Link Table Theory

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

Not applicable

Re: Link Table Theory

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

Community Browser