Skip to main content
Peek - script function

ON THIS PAGE

Peek - script function

Peek() finds the value of a field in a table for a row that has already been loaded or that exists in internal memory. The row number can be specified, as can the table.

Syntax:  

Peek(

field_name

[, row_no[, table_name ] ])

Return data type: dual

Arguments:  

Arguments
Argument Description
field_name Name of the field for which the return value is required.Input value must be given as a string (for example, quoted literals).
row_no

The row in the table that specifies the field required. Can be an expression, but must resolve to 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 A table label 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.

Limitations:  

In the first record of an internal table, the function returns NULL.

Example:  

Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

EmployeeDates:

Load * Inline [

EmployeeCode|StartDate|EndDate

101|02/11/2010|23/06/2012

102|01/11/2011|30/11/2013

103|02/01/2012|

104|02/01/2012|31/03/2012

105|01/04/2012|31/01/2013

106|02/11/2013|

] (delimiter is '|');

 

FirstEmployee:

Load EmployeeCode, Peek('EmployeeCode',0) As EmpCode

Resident EmployeeDates;

 

EmpCode = 101, because Peek('EmployeeCode',0) returns the first value of EmployeeCode in the table EmployeeDates.

Substituting the value of the argument row_no returns the values of other rows in the table, as follows:

Peek('EmployeeCode',2) returns the third value in the table: 103.

However, note that without specifying the table as the third argument table_no, the function references the current (in this case, internal) table. The result of Peek('EmployeeCode',-2) is multiple values:

Resulting table
Employee code EmpCode

101

-
102 -
103 101
104 102
105 103
106 104

Example:  

FirstEmployee:

Load EmployeeCode, Peek('EmployeeCode',-2,'EmployeeDates') As EmpCode

Resident EmployeeDates;

 

By specifying the argument table_no as 'EmployeeDates', the function returns the second-to-last value of EmployeeCode in the table EmployeeDates: 105.

Example:  

The Peek() function can be used to reference data that is not yet loaded.

Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

T1:

LOAD * inline [

ID|Value

1|3

1|4

1|6

3|7

3|8

2|1

2|11

5|2

5|78

5|13

] (delimiter is '|');

T2:

LOAD

*,

IF(ID=Peek('ID'), Peek('List')&','&Value,Value) AS List

RESIDENT T1

ORDER BY ID ASC;

DROP TABLE T1;

 

Create a table in a sheet in your app with ID, List, and Value as the dimensions.

Resulting table
ID List Value
1 6 6
1 6,3 3
1 6,3,4 4
2 11 11
2 11,10 10
2 11,10,1 1
3 8 8
3 8,7 7
5 13 13
5 13,2 2
5 13,2,78 78

The IF() statement is built from the temporary table T1.

Peek('ID') references the field ID in the previous row in the current table T2.

Peek('List') references the field List in the previous row in the table T2, currently being built as the expression is evaluated.

The statement is evaluated as follows:
If the current value of ID is the same as the previous value of ID, then write the value of Peek('List') concatenated with the current value of Value. Otherwise, write the current value of Value only.

If Peek('List') already contains a concatenated result, the new result of Peek('List') will be concatenated to it.

Information noteNote the Order by clause. This specifies how the table is ordered (by ID in ascending order). Without this, the Peek() function will use whatever arbitrary ordering the internal table has, which can lead to unpredictable results.

Example:  

LOAD A, B, numsum( B, Peek( 'Bsum' ) ) as Bsum...;

Creates an accumulation of B in Bsum.