4 Replies Latest reply: Jun 9, 2011 10:27 AM by Waqas Saeed RSS

    Mapping using 2 mapping tables

    Waqas Saeed

      Hello Experts,

       

      A simple question but cant find its answer!

       

      Can we create a column using 2 mapping tables? let me explain what I want...

       

      In total I have 1 data table and 2 mapping tables with 2 columns in each of them.

       

      Data Table

      [ Category ] and [ Sale ]

       

      Mapping tables

      Table 1

      [ From_Filter 1 ] and [ To_Filter 1 ]

       

      Table 2

      [ From_Filter 2 ] and [ To_Filter 2]

       

       

      Now, I want to create another column in the Data Table called Product Group. This Product Group column will be based on differnet conditions using two mapping tables.

       

      Below are the conditions I want to be met while I create Product Group column in data table:

       

      Product Group should be = Enerygy IF From_Filter 1 = Energy and From_Filter 2 = OIL

      Product Group should be = Enerygy IF From_Filter 1 = Energy and From_Filter 2 = GAS

       

      Product Group should be = Asset IF From_Filter 1 = Property and From_Filter 2 = APS

      Product Group should be = Asset IF From_Filter 1 = Property and From_Filter 2 = PROPERTY

      etc

       

       

      I can do this using if conditions but the problem is that these conditions are too many and I cant use if condition for all of them. Thats why I created mapping tables.

       

      Can someone please help me in this?

       

      Thanks

        • Mapping using 2 mapping tables

          How about using only 1 mapping table based on a concatenation of both criterias within your load script?

           

          Data Table

          Load *,

          Category&Sale as [Product Group] //Mapping Criteria

           

          Mapping tables

          Table 1:

          LOAD * INLINE [

              x, y

              EnergyOIL, Enerygy

              EnergyGAS, Enerygy

              PropertyAPS, Asset

              PropertyPROPERTY, Asset

          // etc.

          ];

            • Mapping using 2 mapping tables
              Waqas Saeed

              Hi Gregor,

               

              Thanks for your reply!

               

              I think I didnt explain properly what I am trying to achieve.

               

              Actually I have got 1 table it has 2 Columns. Primary Hierarchy and Secondary Hierarchy. Based on these two columns I want to create another one. Which I would call it as Product Group. So I thought the best way to do it is to export Primary Hierarchy and Secondary Hierarchy and make them as mapping tables. May be this is not correct. May be the solution is something else. I have attached a snapshot of what I want to clear this. Please see attached.

               

              For Energy its simple. Regarless of what Secondary Hierarchy has, Product Group will always be Energy. But for EMP I will need to use Secondary Hierarchy. So If Primary is EMP and Secondary is New Assest then Product Group should be ALP but if Primary is EMP and Secondary its Old Asset then Product Group should be OST.

               

              So on and so forth. I hope this time I have explained it properly. how can I solve this problem? Do I have to use mapping tables?

               

              Please help!

               

              Thanks

              help2.bmp

                • Re: Mapping using 2 mapping tables

                  Maybe I explanation was not clear enough, but the following code achives exactly what you are looking for:

                   

                  Code:

                  MAP:
                  mapping load * Inline [
                       x, y
                       EnergyAVI Liability, Energy
                       EnergyAVI Product, Energy
                       EnergyAVI CRS, Energy
                       EMPNew Asset, ALP
                       EMPOld Asset, OST
                       ARSOIL, Property
                       ARSGAS, Property
                       ARSAPS, Casualty
                       ];
                  
                  map [Product Group] using MAP;
                  
                  Data:
                  Load * Inline [
                  Primary Hierarchy, Secondary Hierarchy, Sale
                  Energy, AVI Liability, 23
                  Energy, AVI Product, 34
                  Energy, AVI CRS, 45
                  EMP, New Asset, 67
                  EMP, Old Asset, 34
                  ARS, OIL, 23
                  ARS, GAS, 45
                  ARS, APS, 76
                  ];
                  
                  MappedData:
                  LOAD *,
                  [Primary Hierarchy] & [Secondary Hierarchy] as [Product Group] 
                  resident Data; 
                  
                  drop table Data;
                  

                   

                  Result:

                  s1.JPG