topic Re: Exchange rate problem in QlikView

Exchange rate problem

Mon, 24 Oct 2011 07:17:28 GMT

Hi,

I have problem with exchange rate. I have two tables A (order_number, order_date, currency) and B (date, currency, exchange_rate). I created key (date&currency) and it's working perfectly, but..

On each Monday I don't have exchange rate value, becaus it's announced on Tuesday. In this case I sholud use exchange rate from last day when it was announced (in this case from Friday). How to do this?

I need this value when I create a straight table and I'm converting currency:

if(Currency<>'PLN',[Ordered quantity]*[Purchase price]*[Exchange rate];[Ordered quantity]*[Purchase price])

Exchange rate problem

Mon, 24 Oct 2011 07:24:29 GMT

Hi,

You can do something like this:

load

if (isnull(exchangerate) and day=monday,above(exchangerate,3),exchangerate) as exchangerate

succes!

Halmar

Re: Exchange rate problem

Mon, 24 Oct 2011 10:32:30 GMT

Hi,

function above() is not working in script, but I can use it in straight table.

I have one more problem. I noted that if in tabel with EX_RATE exchange rate don't exist then I can't use it as key.

Please, look to example below. In straight table 'ORDER VALUE' I would like to have position for date 20111017 and currency EUR,

which consist in column exchange rate value from day before (in this case from 20111014).

Re: Exchange rate problem

Mon, 24 Oct 2011 10:38:09 GMT

Sorry,

You have to use previous() in the script.

I would not use above() in the table, because when you select one day, a monday, it is not working fine.

Halmar