2 values for ApplyMap in currency and year

[bobbydave]

So I have a mapping table

COUNTRY     YEAR           $            €

AT              2013      1.11         1

AT              2015      2              1

BE              2014      1.2           1

DK              2015      1.8           1

UK              2016      1.25         1

UK              2015      1.19         1

In my application, my mapping table consists of

MapCountryToEuro:
Mapping
Load
COUNTRY & '-' & YEAR as COUNTRY_YEAR,
EURO_EXCHANGE_RATE
FROM
[$(vMapping)\Exchange_Rates.xlsx]
(ooxml, embedded labels, table is Sheet1);

MapCountryToDollar:
Mapping
Load
COUNTRY & '-' & YEAR as COUNTRY_YEAR,
DOLLAR_EXCHANGE_RATE
FROM
[$(vMapping)\Exchange_Rates.xlsx]
(ooxml, embedded labels, table is Sheet1);

and in my application I am calling

Table:

Country,

[Snapshot year],

ApplyMap('MapCountryYearToEuro', Country & '-' & [Snapshot Year], 'Null')  as €Country

from .......

My results only show me

AT-2013  

BE-2014  

DK-2015  

UK-2016

ETC

and not the exchange rate.

Any ideas?

[MindaugasBacius · Accepted solution]

map_$:
mapping Load COUNTRY & '-' & YEAR
  ,$
Inline [
COUNTRY,     YEAR,      $,            €
AT,          2013,      1.11,         1
BE,          2014,      1.2,          1
DK,          2015,      1.8,          1
UK,          2016,      1.25,         1
];


Table:
Load Applymap('map_$', COUNTRY & '-' & YEAR, ':') as $
  ,*
Inline [
COUNTRY,     YEAR,      Sales,         
AT,          2013,      111,         
BE,          2014,      12,         
DK,          2015,      18,          
UK,          2016,      125,        
];

Result are attached.

[Anonymous]

only typo error?

'MapCountryYearToEuro' instead of 'MapCountryToEuro'

applymap doesnot return an error if Name of mappingtable does not exist

ApplyMap('MapCountryYearToEuro', Country & '-' & [Snapshot Year], 'Null')  as €Country

[martinpohl]

your mapping tables called

MapCountryToEuro and MapCountryToDollar

but you are using 'MapCountryYearToEuro' in the applymap.

Regards

[bobbydave]

Sorry guys, it was a typo but that was my bad manually typing on this screen.

Wasn't the issue though.

[bobbydave]

Sorry guys, it was a typo but that was my bad manually typing on this screen.

Wasn't the issue though.

[MindaugasBacius]

Please, close the subject if it is solved.

[bobbydave]

Not solved yet.

The typo was my bad on this webpage.

I am following your example at the moment.

[Anonymous]

I used your data and inline tables and got correct values

Fact:
load * Inline [
Country, 'Snapshot year'
AT, 2013
AT, 2015
];

TEST:
load * Inline [
COUNTRY, YEAR, DOLLAR_EXCHANGE_RATE, EURO_EXCHANGE_RATE
AT, 2013, 1.11, 1
AT, 2015, 2, 1
BE, 2014, 1.2 , 1
DK, 2015, 1.8, 1
UK, 2016, 1.25, 1
UK, 2015, 1.19, 1
];

MapCountryToEuro:
Mapping
Load
COUNTRY & '-' & YEAR as COUNTRY_YEAR,
EURO_EXCHANGE_RATE
resident TEST;

MapCountryToDollar:
Mapping
Load
COUNTRY & '-' & YEAR as COUNTRY_YEAR,
DOLLAR_EXCHANGE_RATE
resident TEST;

Table:
load
Country,
[Snapshot year],
ApplyMap('MapCountryToEuro', Country & '-' & [Snapshot year], 'Null')  as €Country
resident Fact;

[bobbydave]

I've attached a sample from my application.

I still cant manage to get the exchange rate back in the mapping

Appears as AT-2015 or UK-2014 when the rate associated should appear instead