topic Re: if calc in QlikView

if calc

raadwiptec — Mon, 09 Nov 2015 16:08:39 GMT

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

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 — Mon, 09 Nov 2015 16:26:37 GMT

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 — Mon, 09 Nov 2015 16:27:35 GMT

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.