Mapping

Wed, 16 May 2012 11:53:12 GMT

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

topic Mapping in QlikView

Mapping