0 Replies Latest reply: May 16, 2012 7:53 AM by Martyn Alford RSS

    Mapping

      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