Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MindaugasBacius
Valued Contributor III

Re: 2 values for ApplyMap in currency and year

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.

Partner
Partner

Re: 2 values for ApplyMap in currency and year

can you post Screen shot of Excel Exchange_rates?

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

bobbydave
Contributor III

Re: 2 values for ApplyMap in currency and year

Here is the Exchange_Rates.xlsx and sample piece of app

Re: 2 values for ApplyMap in currency and year

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

Re: 2 values for ApplyMap in currency and year

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
Contributor III

Re: 2 values for ApplyMap in currency and year

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