Thursday, August 21, 2014

QV13 Peek() and previous()

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.

Both are Inter Record Functions.These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record.  IT loads the value from currently evaluating column. which is being in process and look into the previous value for that column.

peek(fieldname [ , row [ , tablename ] ] )
Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.
Fieldname must be given as a string (single quote).

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.
If no tablename is stated, the current table is assumed.

previous(expression )
Returns the value of expression using data from the previous input record. In the first record of an internal table the function will return NULL because no previous record exists for first value.

The previous function may be nested.
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.

Some similarities and differenceThe Similarities
 - Both allow you to look back at previously loaded rows in a table.
 - Both can be manipulated to look at not only the last row loaded but also previously loaded rows.
The Differences - 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.
 - The Peek() function can easily reference any previously loaded row in the table using the row number in the function  e.g. Peek(‘Employee Count’, 0)  loads the first row. Using the minus sign references from the last row up. e.g. Peek(‘Employee Count’, -1)  loads the last row. If no row is specified, the last row (-1) is assumed.  The Previous() function needs to be nested in order to reference any rows other than the previous row e.g. Previous(Previous(Hires))  looks at the second to last row loaded before the current row.

So, when is it best to use each function?    The previous() and peek() functions could be used when a user needs to show the current value versus the previous value of a field that was loaded from the original file. 
    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.



LOAD F1, F2, Peek(F1) as PeekVal, Peek('F1',1,'DR') as PeekVal1, Previous(F2) as PrevVal ;
// Where F2 >= 200;
F1, F2
A, 100
B, 200
C, 150
D, 320
E, 222
F, 903
G, 666


data of file "9_Peek vs Previous.xlsx"
Date Hired Terminated
1/1/2011 6 0
2/1/2011 4 2
3/1/2011 6 1
4/1/2011 5 2
5/1/2011 3 2
6/1/2011 4 1
7/1/2011 6 2
8/1/2011 4 1
9/1/2011 4 0
10/1/2011 1 0
11/1/2011 1 0
12/1/2011 3 2
1/1/2012 1 1
2/1/2012 1 0
3/1/2012 3 1
4/1/2012 3 4
5/1/2012 2 1
6/1/2012 1 0
7/1/2012 1 0
8/1/2012 3 0
9/1/2012 4 3
10/1/2012 0 3
11/1/2012 2 1
12/1/2012 0 0
1/1/2013 4 2
2/1/2013 2 2
3/1/2013 3 0

[Employees Init]:

rowno() as Row
Date(Date) as Date
If(rowno()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]

[..\datafiles\9_Peek vs Previous.xlsx]
(ooxml, embedded labels, table is

[Employee Count]:

[Employee Count]
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]

Resident [Employees Init] Order By Row asc;

Drop Table
[Employees Init];

image explanation
1. hierd - no of employee hiered
2. Terminated - no. of emp terminated
3. [Employee count] - accumalation(no of working employee - terminated employees)
we used peek function to get the desire result
4. [Employee Var] - employee variation from field [Employee count] using previou().

//now if you observe that we use previous() in different load query, because previous work on pre-loaded field.
//If we try to use previous() in load table "[Employees Init]" then it will not work, because "[Employee Count]" is being calculated in "[Employee Count]" load statement.

No comments:

Post a Comment

web stats