Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
can you post Screen shot of Excel Exchange_rates?
we have only see result, but cannot Change or test anything
Here is the Exchange_Rates.xlsx and sample piece of app
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
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
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
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