Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

dsharmaqv
Contributor III

Mapping Table with Multiple Columns

Hi

Can some one assist me hot to apply map.

I have loaded the mapping table with 4 columns.

For Example

Mapping Table:

CityStateCountryRegion
KolkataWBIndiaASP
ChicagoILUSNA
London-UKEUR
ParisFranceFranceEUR

Table A:

CityProdSales
KolkataABC654
ChicagoCD5555
ParisFJ

59

I want to map City column and populate State, Country And Region.

1 Solution

Accepted Solutions

Re: Mapping Table with Multiple Columns

Sample Script using the Mapping Tables:

MappingTable1:

Mapping

LOAD City,

    State

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

MappingTable2:

Mapping

LOAD City,

    Country

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

MappingTable3:

Mapping

LOAD City,

    Region

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

LOAD City,

  ApplyMap('MappingTable1', City, Null()) as State,

  ApplyMap('MappingTable2', City, Null()) as Country,

  ApplyMap('MappingTable3', City, Null()) as Region,

    Prod,

    Sales

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @2);


Capture.PNG

4 Replies

Re: Mapping Table with Multiple Columns

I guess create 4 mapping tables out of your one table:

MappingTable1:

Mapping

LOAD City,

          State

FROM ...

MappingTable2:

Mapping

LOAD City,

          Country

FROM ...

MappingTable3:

Mapping

LOAD City,

          Region

FROM ....

Or you can simply perform a left join.

mikael_bjaveclo
New Contributor II

Re: Mapping Table with Multiple Columns

Mapping is just to translate one value to an other value.

The syntax for the mapping table is "From", "To". Just two columns.

mapCityToState:

Mapping Load * Inline [

     _mapCity, _mapState

     Kolkata, WB

     Chicago, IL

     Paris, France

];

Table_A:

Load *,

     ApplyMap('mapCityToState', City, 'missing state') As State

From ...

The table created with "Mapping Load" is automaticly dropped when script reload is finnished.

If You want to add the State, Country and Region columns to Your table, and accept NULL as result where mismatch, I recommend "Join" insted.

Table_A:

Load City, Prod, Sales

From ...

Left Join (Table_A)

Load City, State, Country, Region

From ...

Where Exists(City);

Hope this helps.

galax_allu
Valued Contributor

Re: Mapping Table with Multiple Columns

Hi

as sunny said try with left join

TABLE1:

LOAD City,

    State,

    Country,

    Region

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN

TABLE2:

LOAD City,

    Prod,

    Sales

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @2);

Re: Mapping Table with Multiple Columns

Sample Script using the Mapping Tables:

MappingTable1:

Mapping

LOAD City,

    State

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

MappingTable2:

Mapping

LOAD City,

    Country

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

MappingTable3:

Mapping

LOAD City,

    Region

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @1);

LOAD City,

  ApplyMap('MappingTable1', City, Null()) as State,

  ApplyMap('MappingTable2', City, Null()) as Country,

  ApplyMap('MappingTable3', City, Null()) as Region,

    Prod,

    Sales

FROM

[https://community.qlik.com/thread/228389]

(html, codepage is 1252, embedded labels, table is @2);


Capture.PNG

Community Browser