Peek - script function

Example 1

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

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

LastCode = 106 because Peek('EmployeeCode',-1, 'EmployeeDates') returns the last 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, 'EmployeeDates') returns the third value, 103, in the table as the FirstCode.

However, note that without specifying the table as the third argument table_name in these examples, the function references the current (in this case, internal) table.

Example 2

If you want to access data further down in a table, you need to do it in two steps: first, load the entire table into a temporary table, and then re-sort it when using Peek().

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

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

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.

Example 3

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

The field AmountMonthBefore will hold the amount from the previous month.

Here, the row_no and table_name parameters are omitted, so the default values are used. In this example, the following three function calls are equivalent:

• Peek(Amount)
• Peek(Amount,-1)
• Peek(Amount,-1,'Amounts')

Using -1 as row_no means that the value from previous row will be used. By substituting this value, values of other rows in the table can be fetched:

Peek(Amount,2) returns the third value in the table: 7.

Example 4

Data needs to be correctly sorted in order to get the correct results but, unfortunately, this is not always the case. Furthermore, the Peek() function cannot be used to reference data that has not yet been loaded. By using temporary tables and running multiple passes through the data, such problems can be avoided.

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

tmp1Amounts:
Load * Inline
[Month,Product,Amount
2022-01,B,3
2022-01,A,8
2022-02,B,4
2022-02,A,6
2022-03,B,1
2022-03,A,6
2022-04,A,5
2022-04,B,5
2022-05,B,6
2022-05,A,7
2022-06,A,4
2022-06,B,8];

tmp2Amounts:
Load *,
If(Product=Peek(Product),Peek(Amount)) as AmountMonthBefore
Resident tmp1Amounts
Order By Product, Month Asc; 
Drop Table tmp1Amounts;

Amounts:
Load *,
If(Product=Peek(Product),Peek(Amount)) as AmountMonthAfter
Resident tmp2Amounts
Order By Product, Month Desc; 
Drop Table tmp2Amounts;

Explanation

The initial table is sorted according to month, which means that the peek() function would in many cases return the amount for the wrong product. Hence, this table needs to be re-sorted. This is done by running a second pass through the data creating a new table tmp2Amounts. Note the Order By clause. It orders the records first by product, then by month in ascending order.

The If() function is needed since the AmountMonthBefore only should be calculated if the previous row contains the data for the same product but for the previous month. By comparing the product on the current row with the product on the previous row, this condition can be validated.

When the second table is created, the first table tmp1Amounts is dropped using a Drop Table statement.

Finally, a third pass is made through the data, but now with the months sorted in reverse order. This way, AmountMonthAfter can also be calculated.

Result

Example 5

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

Result

Explanation

The load statement Load *, rangesum(SumVal,peek('AccSumVal')) as AccSumVal includes a recursive call where the previous values are added to the current value. This operation is used to calculate an accumulation of values in the script.