Skip to content

Inter-record functions

Inter-record functions are used when a value from previously loaded records of data is needed for the evaluation of the current record.

Exists

Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement.

Exists(field_name[,expr])

Return data type: Boolean

Argument Description
field_name A name or a string expression evaluating to a field name to be searched for. The field must exist in the data loaded so far by the script.
expr An expression evaluating to the field value to look for in the field specified in field-name . If omitted, the current record’s value in the specified field is assumed.
ExampleResult
Exists (Employee) Returns -1 (True) if the value of the field Employee in the current record already exists in any previously read record containing that field.
Exists(Employee, 'Bill') Returns -1 (True) if the field value 'Bill' is found in the current content of the field Employee. The statements `Exists (Employee, Employee)` and `Exists (Employee)` are equivalent.

Employees:
LOAD * inline [
Employee|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
] (delimiter is '|');

Citizens:
Load * inline [
Name|Address
Bill|New York
Mary|London
Steve|Chicago
Lucy|Paris
John|Miami
] (delimiter is '|');
EmployeeAddresses:

Load Name as Employee, Address Resident Citizens where Exists (Employee, Name);

Drop Tables Employees, Citizens;
Exists (Employee)

This results in a table called EmployeeAddresses in the data model, which can be viewed as a table visualization using the dimensions Employee and Address.

The where clause: where Exists (Employee, Name), means only the names from the tableCitizens that are also in Employees are loaded into the new table. The Drop statement removes the temporary tables Employees and Citizens to avoid confusion.

Employee Address
Bill New York
John Miami
Steve Chicago
Replacing the statement in the sample data in the previous example that builds the table EmployeeAddresses with the following, using where not Exists. NonEmployee: Load Name as Employee, Address Resident Citizens where not Exists (Employee, Name); The where clause includes not: where not Exists (Employee, Name), means only the names from the table Citizens that are not in Employees are loaded into the new table.
EmployeeAddress
MaryLondon
LucyParis

Data used in example:

Employees:
LOAD * inline [
Employee|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
] (delimiter is '|');

Citizens:
Load * inline [
Name|Address
Bill|New York
Mary|London
Steve|Chicago
Lucy|Paris
John|Miami
] (delimiter is '|');

EmployeeAddresses:
Load Name as Employee, Address Resident Citizens where Exists (Employee,
Name);

Drop Tables Employees, Citizens;

LookUp

Lookup() looks into a table that is already loaded and returns the value of field_name corresponding to the first occurrence of the value match_field_value in the field match_field_name . The table can be the current table or another table previously loaded.

lookup(field_name, match_field_name, match_field_value [,table_name])

Return data type: dual

Arguments 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).
match_field_name Name of the field to look up match_field_value in. Input value must be given as a string (for example, quoted literals).
match_field_value Value to look up in match_field_name field.
table_name Name of the table in which to look up the value. Input value must be given as a string (for example quoted literals). If table_name is omitted the current table is assumed.

Note

Arguments without quotes refer to the current table. To refer to other tables, enclose an argument in single quotes.

Limitations

The order in which the search is made is the load order, unless the table is the result of complex operations such as joins, in which case, the order is not well defined. Both field_name and match_field_name must be fields in the same table, specified by table_name.

If no match is found, NULL is returned.

Examples and results:

Example Result

The sample data uses the

Lookup('Category', 'ProductID', ProductID, 'ProductList')

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.

ProductList:
Load * Inline [
ProductID|Product|Category|Price
1|AA|1|1
2|BB|1|3
3|CC|2|8
4|DD|3|2
] (delimiter is '|');
 
OrderData:
Load *, Lookup('Category', 'ProductID', ProductID, 'ProductList') as CategoryID
Inline [
InvoiceID|CustomerID|ProductID|Units
1|Astrida|1|8
1|Astrida|2|6
2|Betacab|3|10
3|Divadip|3|5
4|Divadip|4|10
] (delimiter is '|');
 
Drop Table ProductList

The

The Lookup() function is used to build the OrderData table. It specifies the third argument as ProductID. This is the field for which the value is to be looked up in the second argument 'ProductID' in the ProductList, as denoted by the enclosing single quotes.

The function returns the value for 'CategoryID (in the ProductList table), loaded as CategoryID. The drop statement deletes the ProductList table from the data model, because it is not required, which leaves the OrderData table with the following result:

ProductID InvoiceID CustomerID Units CategoryID
1 1 Astrida 8 1
2 1 Astrida 6 1
3 2 Betacab 10 2
3 3 Divadip 5 2
4 4 Divadip 10 3

Note

The Lookup() function is flexible and can access any previously loaded table. However, it is slow compared with the Applymap() function.

Peek

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.

Peek(field_name[, row_no[, table_name]])

Return data type: dual

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.

Examples and results:

ExampleResult
Add the example script to your app and run it. Then add, at least, the fields listed in the 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:
EmployeeCodeEmpCode
101-
102-
103101
104102
105103
106104
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.
IDListValue
166
16,33
16,3,44
21111
211,1010
211,10,11
388
38,77
51313
513,22
513,2,7878
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.

Note 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 npredictable results.

Previous

Previous() finds the value of the expr expression using data from the previous input record that has not been discarded because of a where clause. In the first record of an internal table, the function will return NULL.

Previous(expr)

Return data type: dual

Argument Description
expr The expression or field containing the data to be measured.

The expression can contain nested

previous() functions in order to access records further back. Data are fetched directly from the input source,
making it possible to refer also to fields that have not been loaded, that is,even if they have not been
stored in its associative database.

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

Examples and results:

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.

Example Result
Sales2013:
Load *, (Sales - Previous(Sales) )as Increase Inline [
Month|Sales
1|12
2|13
3|15
4|17
5|21
6|21
7|22
8|23
9|32
10|35
11|40
12|41
] (delimiter is '|');

By using the Previous() function in the Load statement, we can compare the current value of Sales with the preceding value, and use it in a third field, Increase.

Month
1
2
3
4
5
6
7
8
9
10
11
12

Sales
12
13
15
17
21
21
22
23
32
35
40
41
Increase
-
1
2
2
4
0
1
1
9
3
5
1