Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
leobrand
New Contributor II

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
MVP
MVP

Re: Mapping Table DLE

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
3 Replies
vinieme12
Esteemed Contributor II

Re: Mapping Table DLE

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

vinieme12
Esteemed Contributor II

Re: Mapping Table DLE

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

MVP
MVP

Re: Mapping Table DLE

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
Community Browser