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]

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

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

It seems to me that in your mapping you are missing & '-' part.

[Anonymous]

can you post Screen shot of Excel Exchange_rates?

we have only see result, but cannot Change or test anything

[bobbydave]

Here is the Exchange_Rates.xlsx and sample piece of app

[Peter_Cammaert]

Just a few hints for debugging problems with mapping tables:

The example code in your OP is not what you use to get those "wrong" values. If ApplyMap() cannot find the value you are looking for, it will return either

• the original value when no third parameter is supplied (this is what happens in your case, and the script of your posted document indeed doesn't have a third parameter)
• the value of the third parameter if one is present (your examples don't return 'Null' because you don't use a third parameter)

Moreover, if the Mapping table doesn't exist, then ApplyMap() will return NULL. That will happen every time ApplyMap() is being called with a non-existent table name or without one. This is not the case in your document, so your mapping tables existed all along.

IMHO your mapping tables do not contain the expected values. You may easily find out what they do contain by splitting the MAPPING LOAD into something like this, and then observing the table content in Table Viewer (Ctrl-T):

DataForMappingCountryToEuro:

LOAD Field1, Field2

FROM [$(vMapping)\Exchange_Rates.xlsx] (ooxml, embedded labels, table is Sheet1);

MapCountryToEuro:

MAPPING

LOAD ...

RESIDENT DataForMappingCountryToEuro;

And if you don't have the patience to use a technique to find for yourself what is wrong with these translations, look for hyphens in the MapCountryToEuro and MapCountryToDollar LOADS... 

Best,

Peter

[Peter_Cammaert]

Where you able to find what is wrong with this currency conversion rate lookup? If not, compare these two parts from your script:

For example, on the "Mapping" tab:

:

MapCountryToEuro:

Mapping

Load

  COUNTRY_CODE & CURRENCY_CALENDAR_YEAR,

  EURO_EXCHANGE_RATE

FROM

[$(vMapping)\Exchange_Rates.xlsx]

(ooxml, embedded labels, table is Sheet1);

:

and in the LOAD on tab "Offsystem Premium":

:

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

:

Best,

Peter

[bobbydave]

I found it eventually. However, the results were correct.

If you scroll down in the listbox results, the results were showing.

I didn't expect it to show the AT-XXXX or BE-XXXX, the currencies were there at the end of the list boxes, so it was showing what wasn't matching and was mapping also. I just need to create an outer 'if' statement to remove the AT-XXX and this will just give me the currencies alone