I am looking for some help and a possible solution to a problem I have encountered when linking Qlickview to our internal support system.
The main Fact table (Support_Hd) holds a number of fields which just contain internal references (values) which are populated during the entry or update to a log, the internal references are obtained from a Lookup table and the description for these values are also held within the lookup table.
E.g.
Table 1 (Support_Hd)
Log_Ref (Primary Key)
Log_Stat (Log Status) (Int Ref Value - Decode_ref)
Cont_Meth (Contact Method) (Int Ref Value - Decode_ref)
Prod_lkp (Product Lookup) (Int Ref Value - Decode_ref)
Dept_lkp (Department Lookup) (Int Ref Value - Decode_ref)
Table 2 (Lookup)
Decode_ref (Int Ref Value)
Decode_Name
Code
Description
Tag
I am currently having to link the Lookup Table (Table 2) to the Support_Hd Table (Table 1) 4x times to allow me to lookup against the mulitple fields
otherwise the model generate Synthetic Keys.
// Load Support_Hd Table
Support_Hd:
LOAD
'Log_Ref'
'Log_Stat' AS LKP_Link1,
'Cont_meth' AS LKP_Link2,
'Prod_lkp' AS LKP_Link3,
'Dept_lkp' AS LKP_Link4
;
SQL Select *
From Support_Hd;
// Load Lookup Table (1st Loop)
Lookup:
'decode_ref' AS LKP_Link1,
'decode_name',
'code',
'description'
;
SQL Select *
FROM Lookup;
Repeat 4 times for each lookup which makes the Model very messy and I am not sure it helps with the optimisation of the QVW.
I have attempted to use a Mapping Load but this fails because the two comparison fields are not the same even though they contain the same values.
ApplyMap(Map_Lookup, 'LKP_Link1') AS Log_Status
Can anybody help with a possible solution
Thanks
