Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video

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

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
5,952 Views

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
5,952 Views
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
5,952 Views

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
5,951 Views
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
5,951 Views
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
5,951 Views