4 Replies Latest reply: May 23, 2012 2:14 PM by Martyn Alford RSS

    Mapping Tables and linking fields

      I am new to qlikview and looking for some help or a possible solution to a query I have encountered when linking Qlikview with our internal Support System.

       

      There is 1x main FACT table (Support_Hd) which contains a number of fields which are populated with internal references (values) which are

      Generated when a log is raised or edited, these values are based on entries in a Lookup table.

       

      // Load FACT Table

       

      Support_Hd:
      LOAD
      `log_ref`                          AS STHD_LogReference,               (Promary key field)
      //Linking field - Used to link to the Support_Dt table
      `log_ref`                          AS KEY_LogReference,               (Int Ref Value)
      `log_stat`                        AS KEY_LogStatus,                    (Int Ref Value)
      `Cont_Meth`                    AS KEY_ContactMethod,             (Int Ref Value)

      `Prod_lkp`                       AS KEY_ProductLookup,              (Int Ref Value)

      `Dept_lkp`                       AS KEY_DepartmentLookup          (Int Ref Value)

      ;
      SQL SELECT *
      FROM `Support_Hd` where log_dt > {^2012-01-01};

       

      I am currently having to link the Lookup table 4x times for each lookup and this doesn’t seem the best or most optimised solution.

       

      // Load LOOKUP Table

       

      Lookup:
      LOAD
      `decode_ref`                          AS KEY_LogStatus,               (Int Ref Value)
      code,
      description,
      tag,
      ;
      SQL SELECT *
      FROM lookup;

       

      I have attempt to use a Mapping Load command to apply the description to the main FACT table (Support_Hd) but the script

      Fails due to the two comparison fields are not the same even though they contain the same reference values.

       

      Mapping Code below:

       

      MAP_Lookup:
      LOAD
      //`decode_ref`,
      applymap('MapName',`decode_ref`) AS log_stat,
      `description
      ;
      SQL SELECT *
      FROM chsysdec;

       

      Applymap(MAP_Lookup,`log_stat`) AS STHD_LogStatus,

       

      Any help or solution would be much appreciated

       

       

       

       

       

       

       

       

       

       

        • Mapping Tables and linking fields
          Cesar Accardi

          Hi,

          To use the mapping functionality you must first create your map table ( It must have only 2 fields and the keyword MAPPING must preceed the load statement), after that you'll have this table at memory available for the next loads. Example:

           

          MAP_TABLE:
          MAPPING LOAD
          * INLINE[

          from,to
          a,Active

          i,Inactive
          ];

           

          LOAD
          X,
          APPLYMAP('MAP_TABLE',Stats) as Stats
          FROM
          TABLE;

            • Mapping Tables and linking fields

              Hi

              Thanks for your response.

               

              I have generated the Mapping Table as suggested and the script still fails and I still believe this is because the fields are named differently in the two source tables even though they contain the same data and I was hoping there could be away around this.

               

              // Mapping Table

               

              MAP_Lookup:

              MAPPING LOAD

                        'decode_ref'          as     log_stat,

                        description

                        ;

              Select *

              FROM LOOKUP

              where Code='LOST'

               

              // FACT Table

               

              SUPPORT_HD:

              LOAD

                        'Log_Ref',

                        // 'log_stat',

                        Applymap(MAP_Lookup,log_stat)     as     LogStatus

                        ;

              SQL SELECT *

              FROM 'Support_Hd' where log_dt > {^2012-01-01};

               

              The system fails stating the following error:

              Field not found - <MAP_Lookup>

              SQL SELECT *

              FROM 'Support_Hd' where log_dt > {^2012-01-01};

               

              • Re: Mapping Tables and linking fields

                Hi

                Thanks for your response.

                 

                I have generated the Mapping Table as suggested and the script still fails and I still believe this is because the fields are named differently in the two source tables even though they contain the same data and I was hoping there could be away around this.

                 

                // Mapping Table

                 

                MAP_Lookup:

                MAPPING LOAD

                          'decode_ref'          as     log_stat,

                          description

                          ;

                Select *

                FROM LOOKUP

                where Code='LOST'

                 

                // FACT Table

                 

                SUPPORT_HD:

                LOAD

                          'Log_Ref',

                          // 'log_stat',

                          Applymap(MAP_Lookup,log_stat)     as     LogStatus

                          ;

                SQL SELECT *

                FROM 'Support_Hd' where log_dt > {^2012-01-01};

                 

                The system fails stating the following error:

                Field not found - <MAP_Lookup>

                SQL SELECT *

                FROM 'Support_Hd' where log_dt > {^2012-01-01};