Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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