10 Replies Latest reply: Oct 25, 2013 10:16 AM by Dave Vernon RSS

    Mapping Table Excel

    Giuseppe Di Lullo

      I have a problem with a mapping table. I have an Excel table that is loaded into a QlikView project, it's used it to create groupings.

      In practice:

      foglioex.PNG.png


      The first 3 columns are taken from the DB, the third is assigned in the excel file.

      Then:
        - Loads the data from the DB
        - Loads the mappings excel
        -
      Remaps(Raggruppamento) the triples(Cond.Contrattuale-Posizione-AreaProfessionale) in a pivot table.


      The problem is when the same subject A belongs to two different groupings(Raggruppamento)
      For example:

           - Dipendente - Segreteria - Non Professionale - Supporter e amministrativi

           - DIpendente - Consulente - Legale - Consulenti e Assistenti


      in the pivot table shows only one of the two triples (the most recent in chronological order).

      Does anyone have an idea? It
      must use a "mapping table" or something like this?

      Thanks

       

      Beppe

        • Re: Mapping Table Excel

          Hi Giuseppe,

           

          Is the problem in the presentation (pivot table) or that the data hasn't mapped the second group mapping? Perhaps you could include your loadscript if it's the latter.

           

          Dave

            • Re: Mapping Table Excel
              Giuseppe Di Lullo

              I think that the problem is in the presentation. In this way is loaded the excel table:

               

              RIMAP:

              LOAD

                   Cond.Contrattuale as X_CondContrattuale,

                   Posizione as X_Posizione,

                   AreaProfessionale as X_AreaProf,

                   Raggruppamento as X_Valore

              From

              Report_trimestrale_map_3d.xls

              (biff, embedded labels, table is Foglio1$);

               

              And this is the expression of dimension (Raggruppamento) in the pivot table:

               

              = if((DWH_CondContrattuale=X_CondContrattuale and

                   DWH_Posizione = X_Posizione and

                   AreaProfessionale=X_AreaProf),

                   X_Valore, NULL())

               

              where:

              - DWH_CondContrattuale

              - DWH_Posizione

              - AreaProfessionale

              are the fields taken from DB.

               

              Tnks

               

              Giuseppe

                • Re: Mapping Table Excel

                  Hi Giuseppe,

                   

                  Have you thought of using a Mapping Load and  ApplyMap() in the loadscript, rather than trying to do the mapping in the pivot table?

                   

                  mapTable:

                  Mapping Load

                       [Cond.Contrattuale] & '.' & Posizione & '.' & AreaProfessionale as tripleKey,

                       Raggruppamento

                  FROM

                  Report_trimestrale_map_3d.xls

                  (biff, embedded labels, table is Foglio1$);

                   

                  dbTable:

                  LOAD DWH_CondContrattuale,

                       DWH_Posizione,

                       AreaProfessionale,

                       ApplyMap('mapTable',DWH_CondContrattuale & '.' & DWH_Posizione & '.' & AreaProfessionale,Null()) as Raggruppamento

                  FROM dbtable;

                   

                  This is a great primer to using ApplyMap(): http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

                   

                  This way your pivot table should work without need for an expression in the dimension.

                   

                  Kind Regards,

                   

                  Dave

                  • Re: Mapping Table Excel
                    Jason Michaelides

                    If I understand you correctly then you do need a mapping table.  Try:

                     

                    Map_AreaProf:

                    MAPPING LOAD

                         Cond.Contrattuale & '/' & Posizione & '/' & AreaProfessionale

                         Raggruppamento

                    FROM

                    Report_trimestrale_map_3d.xls

                    (biff, embedded labels, table is Foglio1$);

                     

                    Data:

                    LOAD

                         DWH_CondContrattuale

                         ,DWH_Posizione

                         ,AreaProfessionale

                         ,ApplyMap('Map_AreaProf',DWH_CondContrattuale & '/' & DWH_Posizione & '/' & AreaProfessionale,'Unmapped')     AS     Valore

                    ;

                    SQL SELECT....;

                     

                    Hope this helps,

                     

                    Jason