3 Replies Latest reply: Apr 25, 2018 1:07 AM by Jonathan Dienst RSS

    Mapping Table DLE

    Leo Brand

      Using the Order and Customer tables below I am trying to create a mapping table...

       

      Order Table

      OrderIDOrderDateShipperIDFreightCustomerID
      129872007/12/011273
      129882007/12/011654
      129892007/12/022322
      129902007/12/031763

       

      Customer Table  

      CustomerIDNameCountry
      1DataSalesSpain
      2BusinessCorpItaly
      3TechCoGermany
      4MobechoFrance

       

      The mapping table should look like this...

       

      CustomerIDCountry
      1Spain
      2Italy
      3Germany
      4France

       

      Here is the code from the Data Load Editor...

       

      MapCustomerIDtoCountry:

      Mapping LOAD CustomerID, Country From Customers;

       

      Orders:

      LOAD *,

      ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country

      From Orders;

       

      Customers:

      LOAD

          CustomerID,

          Name,

          Country

      FROM [lib://Sheet1.xlsx]

      (ooxml, embedded labels, table is Customers);

       

      Orders:

      LOAD

          OrderID,

          OrderDate,

          ShipperID,

          Freight,

          CustomerID

      FROM [lib://Sheet2.xlsx]

      (ooxml, embedded labels, table is Orders);

       

      ....I attached a screen shot of the error message this code produces, I want the resulting table to look like the table below, can you help?

       

      OrderID OrderDate ShipperID Freight CustomerID Country
      12987 2007-12-011273Germany
        • Re: Mapping Table DLE
          Vineeth Pujari

          Why not just left Join the Country?

           

          Orders:

          LOAD

              OrderID,

              OrderDate,

              ShipperID,

              Freight,

              CustomerID

          FROM [lib://Sheet2.xlsx]

          (ooxml, embedded labels, table is Orders);

           

          left Join (Orders)

           

          Customers:

          LOAD

              CustomerID,

              Country

          FROM [lib://Sheet1.xlsx]

          (ooxml, embedded labels, table is Customers);

          • Re: Mapping Table DLE
            Vineeth Pujari

            Or if you still want to use ApplyMap() then there are two problems in your Current script, if this is actually the order in which you are loading your tables

             

            1) You cannot refer to a table "Customers" before loading it

                 1)MapCustomerIDtoCountry:

                 Mapping LOAD CustomerID, Country From Customers;  << this has to be loaded before you want to reuse the      table

             

                 2)Orders:

             

                 3)Customers:

             

            2) To reload existing Tables you must use the RESIDENT clause  instead of FROM

             

             

            So the correct order of your load script should be as below

             

            Customers:

            LOAD *

            FROM [lib://Sheet1.xlsx]

            (ooxml, embedded labels, table is Customers);

             

             

            MapCustomerIDtoCountry:

            Mapping LOAD CustomerID, Country RESIDENT Customers;

             

            Orders:

            LOAD

                OrderID,

                OrderDate,

                ShipperID,

                Freight,

                CustomerID,

            ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country

            FROM [lib://Sheet2.xlsx]

            (ooxml, embedded labels, table is Orders);

            • Re: Mapping Table DLE
              Jonathan Dienst

              This is what you need:


              MapCustomerIDtoCountry:

              Mapping LOAD CustomerID, Country

              FROM [lib://Sheet1.xlsx]

              (ooxml, embedded labels, table is Customers);

               

              Orders:

              LOAD

                  OrderID,

                  OrderDate,

                  ShipperID,

                  Freight,

                  CustomerID,

                  ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country

              FROM [lib://Sheet2.xlsx]

              (ooxml, embedded labels, table is Orders);