|
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: 102.
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
101
102
103
104
105
106
|
EmpCode
-
-
101
102
103
104
|
|
|
|
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. |
|
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. |
| 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.
|
|
