topic Re: Mapping Table DLE in App Development

Mapping Table DLE

leobrand — Tue, 24 Apr 2018 21:22:14 GMT

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

Re: Mapping Table DLE

vinieme12 — Wed, 25 Apr 2018 03:55:08 GMT

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

vinieme12 — Wed, 25 Apr 2018 04:00:12 GMT

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

jonathandienst — Wed, 25 Apr 2018 05:06:41 GMT

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);