topic Re: 2 values for ApplyMap in currency and year in QlikView

2 values for ApplyMap in currency and year

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: 2 values for ApplyMap in currency and year

MindaugasBacius — Wed, 13 Jul 2016 08:03:52 GMT

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.

Re: 2 values for ApplyMap in currency and year

Anonymous — Wed, 13 Jul 2016 08:08:41 GMT

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

Re: 2 values for ApplyMap in currency and year

martinpohl — Wed, 13 Jul 2016 08:09:22 GMT

your mapping tables called

MapCountryToEuro and MapCountryToDollar

but you are using 'MapCountryYearToEuro' in the applymap.

Regards

Re: 2 values for ApplyMap in currency and year

bobbydave — Wed, 13 Jul 2016 08:31:54 GMT

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

Wasn't the issue though.

Re: 2 values for ApplyMap in currency and year

bobbydave — Wed, 13 Jul 2016 08:31:59 GMT

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

Wasn't the issue though.

Re: 2 values for ApplyMap in currency and year

MindaugasBacius — Wed, 13 Jul 2016 09:17:39 GMT

Please, close the subject if it is solved.

Re: 2 values for ApplyMap in currency and year

bobbydave — Wed, 13 Jul 2016 09:20:40 GMT

Not solved yet.

The typo was my bad on this webpage.

I am following your example at the moment.

Re: 2 values for ApplyMap in currency and year

Anonymous — Wed, 13 Jul 2016 10:42:06 GMT

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;

Re: 2 values for ApplyMap in currency and year

bobbydave — Wed, 13 Jul 2016 13:25:55 GMT

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

Re: 2 values for ApplyMap in currency and year

MindaugasBacius — Wed, 13 Jul 2016 13:36:56 GMT

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.

Re: 2 values for ApplyMap in currency and year

Anonymous — Wed, 13 Jul 2016 13:39:57 GMT

can you post Screen shot of Excel Exchange_rates?

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

Re: 2 values for ApplyMap in currency and year

bobbydave — Wed, 13 Jul 2016 13:43:15 GMT

Here is the Exchange_Rates.xlsx and sample piece of app

Re: 2 values for ApplyMap in currency and year

Peter_Cammaert — Wed, 13 Jul 2016 13:45:06 GMT

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

Peter_Cammaert — Wed, 13 Jul 2016 15:35:30 GMT

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

Re: 2 values for ApplyMap in currency and year

bobbydave — Thu, 14 Jul 2016 07:04:01 GMT

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