Currency exchange issue (daily rates missing some data)

[#1]

Dear Expert,

I have a currency exchange table (mostly daily but it may miss some days which I don't  know when it will miss). I have transaction data table with date. I want get currency converted by this exchange rate table. If the data in transaction table doesn't have associated exchange rate on that day it should use the rate from the closest previous day (e.g. If Fan 7th doesn't have exchange rate then use Feb 6th exchange rate if it has it, if Feb 6th, 5th, 4th, 3rd, 2nd  and 1st don't have exchange rates, then use Jan 31st's exchange rate).

I thought that one way is trying to fill missing exchange rates in the exchange rate table, in this way the transaction data can be associated with exchange rate. But how to achieve it I need suggestion. Is there other nice way to do it without go through the whole exchange rate table for missing date?

Your suggestion is greatly appreciated,

Weidong

[#2]

It will be solved by applying "Generating Missing Data In QlikView" for filling the missing exchange rate. Is there other way better than this?

[Peter_Cammaert]

You're right, the best way is to sort the existing Exchange Rate table (with holes) by ascending date and currency units (FROM/TO) and walk it from top to bottom and fill in the blanks.

But first, you'll have to create a continuous calendar and join it into the existing table.

Can you post an example table/document to base a script on?

Peter

[#4]

Hi Peter,

Thanks for your reply! The idea is good. But the solution provided in the above link only solved single currency to single currency translation. In my case it is not working. I have exchange rate table with multiple currency in it

[From Currency] [To Currency]  Date Rate and I used following script and didn't work for me.

TempTable_Rates:

LOAD [From Currency],

     [To Currency],

     Date,

     Rate

FROM  ExchangeRateTable.QVD

;

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Rates:

NoConcatenate Load

If( IsNull( Rate ), Peek( [From Currency] ), [From Currency] ) as [From Currency],

If( IsNull( Rate ), Peek( [To Currency] ), [To Currency] ) as [To Currency],

Date,

If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate

Resident TempTable_Rates

Order By  [From Currency], [To Currency], Date;

Any suggestion is greatly appreciated!

Weidong

[Peter_Cammaert]

With this script and without any sample data, there is only one problem I can see happening (actually two, but the second one comes after the first one)

If your existing FromTo Currency rates are joined with the Calendar, rows for the missing dates will be added only once. That means that for two exchange rates (for example, USD->JPY and EUR->GBP) with the same missing date, only one calendar date will be added after the JOIN, and the first/last ordered echange rate will win. You'll need to create a FromTo key and (cartesian) JOIN this to the Calendar before adding the Calendar to the prototype Exchange rate table.

There is a great trick with which you can observe problems unfolding during each stage: use a STORE statement to dump every resident table to QVD after it has been created. Use a tool like QViewer (from EasyQlik) to peek in a QVD, and you'll see what is wrong or simply missing.

All this is difficult to explain without an example document. Please create and post one. It will make life easier both for you, me and the other community members. Thanks.

Peter

[#6]

Hi there,

I add for loops to have solved the issue.

Thanks,

Weidong