Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
Henric_Cronström

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
stevedark
Luminary Alumni
Luminary Alumni

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!

0 Likes
6,947 Views
Henric_Cronström

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

0 Likes
6,947 Views
mr_barriesmith
Partner
Partner

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.

0 Likes
6,947 Views
Miguel_Angel_Baeyens

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.

0 Likes
6,946 Views
jeffmartins
Partner
Partner

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

0 Likes
6,946 Views
jeffmartins
Partner
Partner

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

0 Likes
6,946 Views
rwunderlich
Luminary Alumni
Luminary Alumni

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,851 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,851 Views
Henric_Cronström

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

Let vMaxDate = Num(Today())    ;

HIC

4,851 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,851 Views