Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bobbydave
Contributor III

2 values for ApplyMap in currency and year

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?

1 Solution

Accepted Solutions
MindaugasBacius
Valued Contributor III

Re: 2 values for ApplyMap in currency and year

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.

Screenshot_2.jpg

View solution in original post

15 Replies
MindaugasBacius
Valued Contributor III

Re: 2 values for ApplyMap in currency and year

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.

Screenshot_2.jpg

View solution in original post

Partner
Partner

Re: 2 values for ApplyMap in currency and year

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

Partner
Partner

Re: 2 values for ApplyMap in currency and year

your mapping tables called

MapCountryToEuro and MapCountryToDollar

but you are using 'MapCountryYearToEuro' in the applymap.

Regards

bobbydave
Contributor III

Re: 2 values for ApplyMap in currency and year

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

Wasn't the issue though.

bobbydave
Contributor III

Re: 2 values for ApplyMap in currency and year

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

Wasn't the issue though.

MindaugasBacius
Valued Contributor III

Re: 2 values for ApplyMap in currency and year

Please, close the subject if it is solved.

bobbydave
Contributor III

Re: 2 values for ApplyMap in currency and year

Not solved yet.

The typo was my bad on this webpage.

I am following your example at the moment.

Partner
Partner

Re: 2 values for ApplyMap in currency and year

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

Re: 2 values for ApplyMap in currency and year

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