topic How to create good currency conversion rate table and add missing dates? in QlikView

How to create good currency conversion rate table and add missing dates?

Wed, 13 Jan 2016 08:57:15 GMT

Dear all,

I have to prepare good table to store currency convertion rates.

It will be used to convert different currencies into PLN according to specific date.

Fields:

LOAD Date &'-'& Currency as #Key,
       Date,
    Currency,
     Rate

I’ve already concatenate my old source with my current (daily updated from SAP) source of data plus added a records with key consisting of each date that I could found in that table and ‘PLN’ and rate 1.

The problem is how to add missing dates (holidays, Saturdays, Sundays) for which I do not have currency rate in my sources and assign to them rate value from the nearest existing in table day for specific currency?

I have found QV Technical Brief about Generating missing data, and there is some example but without different currencies, only one (attached below).

Could anybody help me?

Re: How to create good currency conversion rate table and add missing dates?

Gysbert_Wassenaar — Wed, 13 Jan 2016 19:00:43 GMT

Something like this:

TempTable_Rates:
Load Date, Currency, Rate From Rates ;

     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')    ;

Join (TempTable_Rates)

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

Rates:

     NoConcatenate Load Date, Currency,
          If( IsNull( Rate ) and Curreny = Previous(Currency), Peek( Rate ), Rate ) as Rate
          Resident TempTable_Rates
          Order By Currency, Date ; /* so that above values can be propagated downwards */

Drop Table MinMaxDate, TempTable_Rates;

Re: How to create good currency conversion rate table and add missing dates?

Thu, 14 Jan 2016 08:16:58 GMT

Hello,

I tried to apply Your code. Unfortunatelly there is something wrong.

It is generating missing dates but only one date and without assigned rates values for specific currencies.

I suppose the reason is that we are first generating missing dates (only one missing date for every day - and it should be couple of tchem - as many as currencies) and the second thing is that after that when we sort table by currencies, so the generated missing dates are "together" because they have empty currency field so assigning previous value is immposible - am I right?

Do You have an idea how to deal with this problem?

Re: How to create good currency conversion rate table and add missing dates?

vilstrup — Wed, 14 Jun 2017 07:00:01 GMT

Did anyone find a solution to this problem ? I am currently sitting with the exact same issue.