QLIK Function
PEEK() VS PREVIOUS() FUNCTIONS
Introduction
Qlikview has many useful and sometimes underused functions that can make your life as a developer a lot easier.
Peek and Previous are two of those functions. The definitions below are taken from QlikView Help.
Previous (expression)
Returns the value of expression using data from the previous input record that was not discarded due to a where clause.
In the first record of an internal table the function will return NULL.
The previous function may be nested in order to access records further back.
Data are fetched directly from the input source, making it possible to refer also to fields which have not been loaded into QlikView, i.e. even if they have not been stored in its associative database.
Examples:
Load *, Sales / previous (Sales) as Increase from …;
Load A, previous (previous (A)) as B from …;
Peek (fieldname [ , row [ , table name ] ] )
Returns the contents of the fieldname in the record specified by row in the internal table tabe name.
Data are fetched from the associative QlikView database.
Fieldname must be given as a string (e.g. a quoted literal).
Row must be an integer. 0 denotes the first record, 1 the second and so on.
Negative numbers indicate order from the end of the table. -1 denotes the last record read.
If no row is stated, -1 is assumed.
Table name is a table label, see Table Labels, without the ending colon. If no table name is stated, the current table is assumed.
If used outside the load statement or referring to another table, the table name must be included.
Examples:
Peek (‘Sales’)
Returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).
Peek (‘Sales’, 2)
Returns the value of Sales from the third record read from the current internal table.
Peek (‘Sales’, -2)
Returns the value of Sales from the second last record read into the current internal table.
Peek ( ‘Sales’, 0, ‘Tab1’ )
Returns the value of Sales from the first record read into the input table labeled Tab1.
Load A, B, numsum (B, peek( ‘Bsum’ ) ) as Bsum…;
creates an accumulation of B in Bsum.
In its simplest form, Peek () and Previous () are used to identify specific values within a table. All the examples in this document are based upon the simple table below:
Set the Scene
Company ABC wants to be able to keep track of monthly employee totals.
Currently they only collect data for month, hires and terminations.
They would like to add fields for Employee Count and Employee Var to see the monthly difference in total employees.
TAB.
FUNCTIONS, FEATURES AND TRICKS USED
To create the Employee Count and the Emloyee Var fields in QlikView, the following QlikView functions, features and/or tricks were used:
Use of Peek() function
Use of Previous() function
Use of RecNo() function
This application assumes that the current date is April 1, 2013.
Peek () Function
As mentioned earlier, in its simplest form the peek() function lets you identify any value loaded for a defined field.
If(row no()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
In this expression we first look to see if the rowno () is equal to 1. If it is equal to 1, no Employee Count will exist so we will just populate the field with the difference of Hired minus Terminated. If the rowno() is greater than 1, we know that there will be and Employee Count field so we will look at last month’s Employee Count and use that number to add to the difference of that month’s Hired minus Terminated employees. Notice too that in the Peek() function we are using a (-1). This tell QlikView to look at the record above the current record. If the (-1) isn’t specified, QlikView will assume that you want to look at the previous record.
Two important things to remember about the Peek () function are:
When using the Peek() function you should think about using the order by clause to ordering the file
When using the Peek() function, you can specify the exact row that you want to reference.
When using the Peek() function, you can reference a field that was not previously loaded. In this example, Employee Count was not part of the original file and we are creating the field on the fly in the script.
[Employees Init]:
LOAD
rowno() as Row,
Date(Date) as Date,
Hired,
Terminated,
If(rowno()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
From
[Peek vs Previous.xlsx]
(ooxml, embedded labels);
After loading the file, you can see the Employee Count field was correctly calculated using the Peek() function.
TAB2
Previous () Function
As mentioned earlier, in its simplest form the Previous() function lets you identify the last value loaded for a defined field.
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
In this expression we first look to see if the rowno() is equal to 1. If it is equal to 1, we know that there will be no Employee Var because there is no record for the previous month’s Employee Count so we simply enter 0 for the value. If the rowno() is greater than 1, we know that there will be and Employee Count field so we will look at last month’s Employee Count and use that number to subtract that number from the current month’s Employee Count to create a field named [Employee Var].
Two important things to remember about the Previous() function are:
When using the Previous() function you should think about using the order by clause to ordering the file.
You can only reference a previously loaded field when using the Previous() function.
Anytime that you use the Previous() function, you could also use Peek() instead.
[Employee Count]:
LOAD
Row,
Date,
Hired,
Terminated,
[Employee Count],
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
After loading the file, you can see the Employee Var field was correctly calculated using the Previous() function.
Tab3
Summary
Peek () and Previous () allow users to target defined rows within a table.
The biggest difference between the two functions is that the Peek() function allows the user to look into a field that was not previously loaded into the script whereas the Previous() function can only look into a previously loaded field. Previous() operates on the Input to the Load statement, whereas Peek() operates on the Output of the Load statement. (Same as the difference between RecNo() and RowNo().) This means that the two functions will behave differently if you have a Where-clause.
So the previous () function would be better suited for when a user needs to show the current value versus the previous value. In the example we calculated the employee variance from month to month.
The peek () function would be better suited when the user is targeting either a field that has not been previously loaded into the table or if the user needs to target a specific row. This was shown in the example where we calculated the Employee Count by peeking into the previous month’s Employee Count and adding the difference between the hired and terminated employees for the current month. Remember that Employee Count was not a field in the original file.
