Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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?

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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;


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

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?

Highlighted
Contributor III
Contributor III

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

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