Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Sometimes when you load data into QlikView you find that a field is sparsely populated, i.e. it has discrete enumerable values where some values are missing.

 

JoinPeekTable1.pngIt could be like in the table to the right where you have three dates, each with some kind of conversion rate. The table only contains the dates where the conversion rate changed, not the dates between the changes.

 

However, the user will want to ask the question: “What was the status on this specific day?” In other words, the user wants to be able to click on a reference date to see the number that is associated with this date – but the date might not exist in the source data.

 

In such a situation, you need to generate the missing dates between the changes as individual records and use the “Rate” value from the previous date.

 

There are several ways to do this in QlikView, and all of them involve some script programming, using temporary tables. One algorithm is

 

  1. Load the source table containing the rates (below called “Rates”).
  2. Find largest and smallest date in the “Rates” table.
  3. Generate all dates between the largest and smallest dates (below called “Dates”)..
  4. Join the “Dates” table (outer join) onto the “Rates” table.
  5. Sort the resulting table according to date.
  6. Propagate the value of “Rate” downwards to all records that have NULL in the “Rate” field, using the Peek() function.

 

Visually, the join and peek steps of the algorithm look like this:

 

JoinPeekProcess100.png

 

In the QlikView script, the algorithm would look like the following:

 

     TempTable_Rates:
     Load Date, 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,
          If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate
          Resident TempTable_Rates
          Order By Date ; /* so that above values can be propagated downwards */

 

     Drop Table MinMaxDate, TempTable_Rates;

 

Problem solved!

 

This method can be adapted for most situations when you need to insert additional records in existing data: Warehouse balances, Exchange rates, etc.

 

See more in the Technical Brief: Generating Missing Data In QlikView.

 

HIC

43 Comments
MVP & Luminary
MVP & Luminary

Jeff,

I found the FieldValues() method to be the fastest. However, I've always done it like this:

LOAD max(FieldValue('Date', recno())) AUTOGENERATE FieldValueCount('Date');

I don't know if this is detectably faster or slower than your while loop.

If the field exists in more than one table, and you want the values from a specific table, of course you have to use to RESIDENT method. In the subroutine posted here http://community.qlik.com/message/313808#313808, I'm using RESIDENT because I can't be sure if the field appears in more than one table.

-Rob

4,281 Views
Not applicable

Hi Henric

Can we extend this table till today? Assuming today is 2013-01-12 and the rate has not changed since 2013-01-08

0 Likes
4,281 Views

Yes, just change the definition of vMaxDate, e.g:

Let vMaxDate = Num(Today())    ;

HIC

4,281 Views
Not applicable

Hello Henric,

I'm a beginner at Qlikview, but I had a basic question about your suggested algorithm.

I am specifically having difficulty with your temp table:

TempTable_Rates:
Load Date, Rate From Rates ;

When I've tried to replicate this table for my own purposes, I get the following error:

Cannot open file 'C:\Users\Steve Giardina\Desktop\Rates

Is there some syntax I'm missing to get my script to reference the Rates table at the end of the algorithm?

Thanks!

0 Likes
4,281 Views
Creator II
Creator II

Steve,

the "FROM" keyword is for when you want to load from a file; to load

from an internal table, use "RESIDENT" i.e.

TempTable_Rates:

Load Date, Rate RESIDENT Rates ;

Kind Regards,

Angus.

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential, may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ Bank New Zealand Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."

0 Likes
4,281 Views
Not applicable

Hi Henric,

Is the reverse population possible? I mean I have data as below (as of month end). I want to populate it on all month dates, for example, 01-01-2013 to 31-01-2013 has rate of 0.98

Date               rate

31-01-2013     0.98

28-02-2013     0.9776

31-03-2013     0.9766

0 Likes
4,281 Views