
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if calc
hi all,
I have the following scenario
I have a currency exchange rate file
country ¦ Currency¦ year¦ month¦ Rate
uk pound 2015 01 1.31
usa usd 2015 01 4.82
uae dhr 2015 01 1.02
uk pound 2015 02 1.39
I have another table in qlikview
jan | feb | mar | |
product | price | price | price |
a | 10 | 10 | 10 |
b | 20 | 20 | 20 |
the price has to be multiplied by the rate. It has to check the month and year and rate from the exchange rate file only for uk
for example
jan should be 10 * 1.31
feb should be 10 * 1.39
- Tags:
- new_to_qlikview

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, If you only need uk exchanges you can map those values using a year-month key, ie:
Exchanges:
Mapping LOAD year & month as Key, Rate
From .... where country='uk';
Data:
LOAD ...
price * ApplyMap('Exchanges', Year & Num(MonthField, '00')) as price
There is 3-parameter applyMap() function wich can set a default value when the key wasn't found

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
you can use the applymap function to get it:
rate_table:
MAPPING LOAD
month,
rate
FROM ...
where currency = 'pound';
exchange:
LOAD product,
month,
price,
price * ApplyMap('rate_table', month, 1) as converted_price
FROM ...;
I only considered month as key field, but probably you need to create a compound key like year + month + country.
In my example I filtered by currency = pound, but, anyway this is the way to do it.
Also, if you want you can join both tables and do it in that way, but I thinkk this is easier.
