Interesting Behavior With The Previous() Function In a Load Script

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.

Leave a Reply

Your email address will not be published. Required fields are marked *