Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leobrand
Contributor III
Contributor III

Mapping Table DLE

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
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein