There are many examples on the Qlik Community site that address the fundamental difference between Previous() and Peek(), but recently, I discovered an interesting behavior that I didn’t see covered in any of the articles I’d read.
First, here is an example of the basic difference between the two functions.
Previous() returns the previous record in the data source—that is, the data that is being used as the source for the load. Peek() returns records from the data set being loaded—the target of the Load statement.
Suppose we have a table of 100 records that contains a sequentially numbered ID field.
Numbers
ID
1
2
3
.
.
100
Now we execute the following script.
MyTable:
NoConcatenate Load
ID – 1 as ID,
Previous(ID) as Previous_ID_1,
Peek(‘ID’) as Peek_ID_1
Resident Numbers;
Both Previous() and Peek() behave as expected and the resulting table “MyTable” looks as follows.
MyTable
ID Previous_ID Peek_ID
0 – –
1 1 0
2 2 1
3 3 2
… etc.
This is because Previous_ID is the ID of the previous record in the data source, “Numbers”, and Peek_ID is the previous record in the Loaded table “MyTable”. This is consistent with the published definition of how each function works.
The interesting behavior comes when you filter the data source, for example, with a Where Clause. Let’s execute the following script that loads only the odd IDs from source table “Numbers”.
MyTable:
NoConcatenate Load
ID,
Previous(ID) as Previous_ID,
Peek(‘ID’) as Peek_ID
Resident Numbers
Where Mod(ID, 2) <> 0;
We’d expect the resulting table “MyTable” to look as follows because only the odd IDs are being read from the source.
MyTable
ID Previous_ID Peek_ID
1 – –
3 1 1
5 3 3
7 5 5
… etc.
But, this is not what the table looks like! Instead, it looks like this.
MyTable
ID Previous_ID Peek_ID
1 – –
3 2 1
5 4 3
7 6 5
… etc.
It appears that when Previous() reads the prior record from a resident data source it ignores any filtering that is defined in the Where Clause. In other words, Previous() reads the unfiltered source data.
This isn’t what you’d intuitively expect, but something that you should keep in mind when using Previous(). The help text doesn’t specifically mention this. I hope this post saves everyone a few hours of debugging.
Enjoy.