Discussion board where members can get started with QlikView.
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
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
jan should be 10 * 1.31
feb should be 10 * 1.39
Hi, If you only need uk exchanges you can map those values using a year-month key, ie:
Mapping LOAD year & month as Key, Rate
From .... where country='uk';
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
you can use the applymap function to get it:
where currency = 'pound';
price * ApplyMap('rate_table', month, 1) as converted_price
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.