Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

raadwiptec
Contributor II

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 febmar
productpricepriceprice
a101010
b202020

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

2 Replies

Re: if calc

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

josemanuel_cayo
New Contributor III

Re: if calc

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.