Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Using the Order and Customer tables below I am trying to create a mapping table...
Order Table
OrderID | OrderDate | ShipperID | Freight | CustomerID |
---|---|---|---|---|
12987 | 2007/12/01 | 1 | 27 | 3 |
12988 | 2007/12/01 | 1 | 65 | 4 |
12989 | 2007/12/02 | 2 | 32 | 2 |
12990 | 2007/12/03 | 1 | 76 | 3 |
Customer Table
CustomerID | Name | Country |
---|---|---|
1 | DataSales | Spain |
2 | BusinessCorp | Italy |
3 | TechCo | Germany |
4 | Mobecho | France |
The mapping table should look like this...
CustomerID | Country |
---|---|
1 | Spain |
2 | Italy |
3 | Germany |
4 | France |
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-01 | 1 | 27 | 3 | Germany |
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);
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);
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);
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);