How to populate a sparsely populated field - Qlik Community

hic · ‎2013-02-05

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.

It 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

Load the source table containing the rates (below called “Rates”).
Find largest and smallest date in the “Rates” table.
Generate all dates between the largest and smallest dates (below called “Dates”)..
Join the “Dates” table (outer join) onto the “Rates” table.
Sort the resulting table according to date.
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:

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

stevedark · ‎2013-02-05

I like the use of the inline Peek here, not something I have tried before.

Something I have found is with large amounts of data that doing a Min and Max on the table to get the first and last dates can be time consuming in the load. In these cases I will sometimes let the DB do the work and call a separate SQL query to give the Min and Max values rather than a Resident load.

Thanks for the ever interesting posts Henrich!

hic · ‎2013-02-05

Glad you like it. The inline Peek() is something I use quite often. It's also very useful for making accumulations in the script.

Concerning the Min and Max: I absolutely agree! If you have large amounts of data and a good DB, there is no reason to do anything else than an SQL query.

HIC

mr_barriesmith · ‎2013-02-06

Hi. If you have large volume Resident table and you want to get max or min then take a look at the field functions... it is hard to believe how fast this is.

fieldvalue() - QlikView is storing the field as a column of data and this function allows you to get to that column and fieldvaluecount() can get you to the max.

Miguel_Angel_Baeyens · ‎2013-02-06

Good post as usual Henric,

I've addressed this before using IntervalMatch() as in this post (and variations on the same theme). What I have not tested though is how does this performs agains large datasets.

Regards.

jeffmartins · ‎2013-02-07

Hi,

I've had time consuming problems using resident load to get min and max dates and the fieldvalue() function saved me a lot of time.

Instead of using resident load I've created MinMaxDate table as follow:

MinMaxDate:

Load min(Date) as MinDate,

max(Date) as MaxDate;

Load FieldValue('Date',IterNo()) as Date

autogenerate(1)

while not IsNull(FieldValue('Date',Iterno()));

Nice post HIC

jeffmartins · ‎2013-02-15

Hi,

Does anyone know if there is a better way to get min and max dates ?

The previous MinMaxDate table was the fastest I got after doing some tests.

Regards

rwunderlich · ‎2013-02-20

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

Report Inappropriate Content · ‎2013-05-22

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

hic · ‎2013-05-22

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

Let vMaxDate = Num(Today()) ;

HIC

Report Inappropriate Content · ‎2013-07-29

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!