if calc

[raadwiptec]

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

[rubenmarin]

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

[Anonymous]

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.