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:
| 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 1:
Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document 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:
| EmployeeCode | EmpCode |
|---|---|
| 101 | - |
| 102 | - |
| 103 | 101 |
| 104 | 102 |
| 105 | 103 |
| 106 | 104 |
Example 2:
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 3:
The Peek() function can be used to reference data that is not yet loaded.
Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document 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 document with ID, List, and Value as the dimensions.
| 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.
