Filling the missing dates for exchange rates

[dimak123]

Hi to all,

i am new to qlikview

Looking for a script...

i am trying to fill the missing dates, and while it filling the dates it will take the proper exchange rate by the currency

thanks a head

[agomes1971]

Hi,

please see this thread Generating Missing Data In QlikView

HTH

André Gomes

[dimak123]

thanks

but how do i split between currencies ?

[robert_mika]

Page 7 of Henric's documents give you solution.(from Andre's link).

Otherwise post sample of your data

[martinpohl]

so order the datas by date and currency

load....

from ....

order by date, currency;

and check in the script if the currency is changing

If( IsNull( Rate ),

if(peek(currency)=currency,

Peek( Rate ), Rate ),Rate) as Rate

Regards

Martin

by the way: you can't load datas from a qvd by using order by.

Then you have to laod the datas from qvd and reload them by resident.

[dimak123]

still having holes...

[martinpohl]

post your script for the currency-datas here.

I believe the datas are missing date-values.

What join did you used? you need an outer join to create all datas who are not in your database (e.g. jan 5th may was a weekend)

Regards

[dimak123]

i know it was a weekend, i need all dates

thanks a head

-------------------------

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;

outer Join (TempTable_Rates)

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

Rates:

NoConcatenate Load Date,

If( IsNull( Rate ),if(peek(currency)=currency,Peek( Rate ), Rate ),Rate) as Rate,currency

Resident TempTable_Rates

Order By currency,Date desc ;

Drop Table TempTable_Rates;

[martinpohl · Accepted solution]

that's right.

By joining the date the currency isn't filled , so you can't verify it by peek

add the currency with all dates like this:

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;

Date_Currency:

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

//here join all currency from your datas

left join load distinct currency resident datas;

//join all dates and all currencys to datas

outer Join (TempTable_Rates) load * resident Date_Currency;

//drop temporary table

drop table Date_Currency;

Rates:

NoConcatenate Load Date,

If( IsNull( Rate ),if(peek(currency)=currency,Peek( Rate ), Rate ),Rate) as Rate,currency

Resident TempTable_Rates

Order By currency,Date desc ;

Drop Table TempTable_Rates;

[dimak123]

great

thanks a lot