QlikView’s Previous() and Peek() are not the same

By:

Peek() and Previous() read data from different locations creating a pitfall for the developer but also an opportunity. Understanding the difference ensures optimal performance and fewer coding exceptions

To state Previous() and Peek() are different may sound like a fairly obvious statement and I wouldn’t criticise you (much) if your prime reason for coming here was to lament my statement of the obvious in the comments below. However, there is a subtle but key difference between the two functions that recently caught me out so I thought it may be helpful to share. According to Qlik’s documentation:

peek(Sales) returns the value of Sales in the previous record read (equivalent to previous(Sales))

I’ve found this statement to be not quite the case and deeper understanding of the key differences between the 2 functions will help to ensure you use the correct function for the correct use case.

The Key Difference

The key difference between these two functions is where they read records from. Previous() reads directly from the data source while Peek() reads from data that is already in memory. Think that amounts to the same thing? Think again; lets look at some test data:

A table of data showing consecutive dates but missing records.
A table of data showing consecutive dates but missing records.

I recently had a requirement to populate missing records with the previously loaded field value if the current value was not populated. My first thought was to use the Previous() function because according to the documentation it Returns the value of expression using data from the previous input record. The Peek() function felt like it would be overkill given all its different available parameters (more about that later). Here was my code:

Load
 Date,
 if(isNull(Unit),Previous(Unit),Unit) as Unit,
 if(isNull(Rate),Previous(Rate),Rate) as Rate
Resident T1;

And the results:

Populating missing fields using the Previous() function.
Populating missing fields using the PREVIOUS() function.

Comparing this with the original table, you will notice that only 1 field has been populated following a record that contains data. This is because the Previous() function is reading directly from the source (in this case, a resident table, but this could just as well be a database, flat file, Excel document etc). When it finds a record that contains a null value, it reads the value from the immediately previous row in the original dataset. So of course, when it increments on a further record, the previous row in the original dataset now contains a null, and as such, that is what is displayed.

Now lets see what happens now when we use Peek(). First the code:

Load
 Date,
 if(isNull(Unit),Peek(Unit),Unit) as Unit,
 if(isNull(Rate),Peek(Rate),Rate) as Rate
Resident T1;

And the results:

Populating missing fields using the Previous() function.
Populating missing fields using the PEEK() function.

Ta-da! All of the records are now populated. Why is this? Well, the documentation says that Peek(): … returns the contents of the fieldname in the record specified by row in the internal table tablename.. What this means is that Peek() is reading directly from tables already loaded into memory and as such includes any processing that was done on that field/row as that field was written into memory. A subtle, but important difference!

Peek() is way more useful than just this use case though

Peek() is not limited to reading just the previously loaded record from the table you are currently processing. It can read a specific field from any row in any previously loaded table. I don’t know what we’d do without it.

…don’t write off Previous()

In this example, Previous() got a bit of a bad rap however it shouldn’t be considered as Peek()‘s poor cousin. Because of its ability to read directly from the data source, it becomes a useful tool for building things like running totals using data from fields that you haven’t loaded.


5 Replies to “QlikView’s Previous() and Peek() are not the same”

  1. Yopu have the same subtitle for the graphics twice. Below the 2nd graphic, it should read: “Populating missing fields using the PEEK() function.”

Leave a Reply

Your e-mail address will not be published. Required fields are marked *