QlikView’s Previous() and Peek() are not the same
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
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:
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:
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:
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
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.