{"id":112,"date":"2018-07-15T17:44:59","date_gmt":"2018-07-15T17:44:59","guid":{"rendered":"http:\/\/qlikwithfriends.com.temp.realssl.com\/?p=112"},"modified":"2018-11-26T05:48:49","modified_gmt":"2018-11-26T05:48:49","slug":"interesting-behavior-with-the-previous-function-in-a-load-script","status":"publish","type":"post","link":"https:\/\/qlikwithfriends.com\/index.php\/2018\/07\/15\/interesting-behavior-with-the-previous-function-in-a-load-script\/","title":{"rendered":"Interesting Behavior With The Previous() Function In a Load Script"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

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\u2019t see covered in any of the articles I\u2019d read.<\/p>

\u00a0<\/p>

First, here is an example of the basic difference between the two functions.<\/p>

\u00a0<\/p>

\u00a0<\/p>

Previous() returns the previous record in the data source\u2014that is, the data that is being used as the source for the load. Peek() returns records from the data set being loaded\u2014the target of the Load statement.\u00a0<\/p>

\u00a0<\/p>

\u00a0<\/p>

Suppose we have a table of 100 records that contains a sequentially numbered ID field.<\/p>

\u00a0<\/p>

\u00a0<\/p>

Numbers<\/b><\/p>

ID<\/u><\/p>

1<\/p>

2<\/p>

3<\/p>

.<\/p>

.<\/p>

100<\/p>

\u00a0<\/p>

\u00a0<\/p>

Now we execute the following script.<\/p>

\u00a0<\/p>

\u00a0<\/p>

MyTable:<\/em><\/p>

NoConcatenate Load<\/em><\/p>

ID – 1 as ID,<\/em>
Previous(ID) as Previous_ID_1,<\/em><\/p>

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Peek(‘ID’) as Peek_ID_1<\/em><\/p>

Resident Numbers;<\/em><\/p>

\u00a0<\/p>

\u00a0<\/p>

Both Previous() and Peek() behave as expected and the resulting table \u201cMyTable\u201d looks as follows.<\/p>

\u00a0<\/p>

\u00a0<\/p>

MyTable<\/b><\/p>

ID<\/u>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Previous_ID\u00a0\u00a0\u00a0\u00a0\u00a0Peek_ID<\/p>

0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–<\/p>

1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00
2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01
3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02
\u2026 etc.<\/p>

\u00a0<\/p>

\u00a0<\/p>

This is because Previous_ID is the ID of the\u00a0previous\u00a0<\/i>record in the data source, “Numbers”, and Peek_ID is the\u00a0previous\u00a0<\/i>record in the Loaded table \u201cMyTable\u201d. This is consistent with the published definition of how each function works.<\/p>

\u00a0<\/p>

\u00a0<\/p>

The interesting behavior comes when you filter<\/u> the data source, for example, with a Where Clause. Let\u2019s execute the following script that loads only the odd IDs from source table \u201cNumbers\u201d.<\/p>

\u00a0<\/p>

\u00a0<\/p>

MyTable:<\/em><\/p>

NoConcatenate Load<\/em><\/p>

ID,<\/em>
Previous(ID) as Previous_ID,<\/em>
Peek(‘ID’) as Peek_ID<\/em><\/p>

Resident Numbers<\/em><\/p>

Where Mod(ID, 2) <> 0;<\/em><\/p>

\u00a0<\/p>

\u00a0<\/p>

We\u2019d expect the resulting table \u201cMyTable\u201d to look as follows because only the odd IDs are being read from the source.<\/p>

\u00a0<\/p>

\u00a0<\/p>

MyTable<\/b><\/p>

ID<\/u>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Previous_ID\u00a0\u00a0\u00a0\u00a0\u00a0Peek_ID<\/p>

1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–<\/p>

3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01
5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03
7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05
\u2026 etc.<\/p>

\u00a0<\/p>

\u00a0<\/p>

But, this is not<\/u> what the table looks like! Instead, it looks like this.<\/p>

\u00a0<\/p>

\u00a0<\/p>

MyTable<\/b><\/p>

ID<\/u>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Previous_ID\u00a0\u00a0\u00a0\u00a0\u00a0Peek_ID<\/p>

1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0–<\/p>

3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01
5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03
7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05
\u2026 etc.<\/p>

\u00a0<\/p>

\u00a0<\/p>

It appears that when Previous() reads the prior record from a resident data source\u00a0it ignores any filtering that is defined in the Where Clause<\/u><\/i>. In other words, Previous() reads the unfiltered<\/u> source data.<\/p>

\u00a0<\/p>

\u00a0<\/p>

This isn\u2019t what you\u2019d intuitively expect, but something that you should keep in mind when using Previous(). The help text doesn\u2019t specifically mention this. I hope this post saves everyone a few hours of debugging.<\/p>

\u00a0<\/p>

Enjoy.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

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\u2019t see covered in any of the articles I\u2019d read. First, here is an example of the basic difference between the two functions. …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[6,9],"tags":[],"class_list":["post-112","post","type-post","status-publish","format-standard","hentry","category-qliksense","category-scripting"],"_links":{"self":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/comments?post=112"}],"version-history":[{"count":3,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/112\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/112\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}