5 Replies Latest reply: Jun 28, 2012 5:16 AM by Rich Hemmings RSS

    Mapping Load - Apply Map Question

      My understanding is that when you use the command applymap, the script, looks in the table you mapped, finds the field you specify, and then returns the value of that field.  I am reviewing someone elses work and I see what it is doing but it does make sense.  In this statement,

       

      applymap('mapCustClass', [Customer Customer Class Code], 'Unassigned') as [Customer Customer Class],

       

      The field [Customer Customer Class Code] does not exist in the mapCustClass table, only A1 and A2 exist.  However, the value being returned in the applymap is the value of A2.  So, how can the applymap function find the field [Customer Customer Class Code] (which originally was the [Customer classific._KUKLA] field) in the customer table and know that field is the A2 field in the mapCustClass table that was mapped?  I am assuming this is what it is doing by the results being generated.  Can someone help me understand this a bit better?  Thanks,

       

      mapCustClass:
      MAPPING
      LOAD DISTINCT
      [Customer classific._KUKLA] as a1,
      [Customer classific._KUKLA] & '-' & Description_VTEXT as a2
      FROM TKUKT.qvd (qvd)

       

      Customer:
      LOAD
      Name_NAME1 as [Customer Name],

      Customer_KUNNR,
      [Customer classific._KUKLA] as [Customer Customer Class Code]

       

      FROM ZBI_KNA1.qvd (qvd)

       

       

      Left join (Customer)
      LOAD DISTINCT
      Customer_KUNNR,
      applymap('mapCustClass', [Customer Customer Class Code], 'Unassigned') as [Customer Customer Class],

       

      RESIDENT Customer;

       

       

       

       

       

        • Mapping Load - Apply Map Question
          Stefan Wühl

          The field names in the mapping table are not relevant, it just uses the first field to match the given value and returns the corresponding value in the second field of the mapping table.

           

          Please have a look into Steve's blog:

          http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

           

          Regards,

          Stefan

          • Re: Mapping Load - Apply Map Question
            Miguel Angel Baeyens de Arce

            Hi,

             

            Your thoughts are right: mapping tables don't care about field names. If you prefer, you can call the first field the input field and the second, the output field. Whenever the second parameter of the ApplyMap() function finds a value in the mapping table specified as the first parameter, it will always return the value in the second field. However, this is not the only function for mapping tables. You can change field names or table names as well using a mapping table. Again, in this case the mapping table field names don't care either.

             

            Think of this mapping table

             

            TitleMap:
            MAPPING LOAD * INLINE [
            Short, Long
            Mr., Mister
            Dr., Doctor
            Ms., Miss
            ];
            
            Customers:
            LOAD CustomerID,
                 CustomerName,
                 CustomerTitle
                 ApplyMap('TitleMap', CustomerTitle) AS CustomerLongTitle
            FROM CustomerTable;
            

             

            The ApplyMap might read: "check the table TitleMap for the input value of CustomerTitle and return the output value and store it into a field named  CustomerLongTitle".

             

            That's the way mapping tables work.

             

            Hope that makes sense.

             

            Miguel

            • Re: Mapping Load - Apply Map Question

              Is it possible to use the mapping load to include a 'catch all' expression?

              i.e

              CASE [field]

              WHEN aTHEN 1

              WHEN b THEN 2

              .

              .

              ELSE 99