topic Re: Filling the missing dates for exchange rates in QlikView

Filling the missing dates for exchange rates

dimak123 — Fri, 06 Mar 2015 12:31:00 GMT

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

Re: Filling the missing dates for exchange rates

agomes1971 — Fri, 06 Mar 2015 12:34:12 GMT

Hi,

please see this thread Generating Missing Data In QlikView

HTH

André Gomes

Re: Filling the missing dates for exchange rates

dimak123 — Fri, 06 Mar 2015 13:05:27 GMT

thanks

but how do i split between currencies ?

Re: Filling the missing dates for exchange rates

robert_mika — Fri, 06 Mar 2015 13:14:58 GMT

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

Otherwise post sample of your data

Re: Filling the missing dates for exchange rates

martinpohl — Fri, 06 Mar 2015 13:19:16 GMT

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.

Re: Filling the missing dates for exchange rates

dimak123 — Fri, 06 Mar 2015 13:38:01 GMT

still having holes...

Re: Filling the missing dates for exchange rates

martinpohl — Fri, 06 Mar 2015 13:46:18 GMT

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

Re: Filling the missing dates for exchange rates

dimak123 — Fri, 06 Mar 2015 13:52:33 GMT

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;

Re: Filling the missing dates for exchange rates

martinpohl — Fri, 06 Mar 2015 14:14:58 GMT

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;

Re: Filling the missing dates for exchange rates

dimak123 — Fri, 06 Mar 2015 14:18:11 GMT

great

thanks a lot