LookUp - script function

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.

Syntax:

lookup(field_name, match_field_name, match_field_value [, 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).
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.

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.

Example

Result

The sample data uses the Lookup() function in the following form:

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 ProductList table is loaded first.

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 'Category' (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

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

Learn more

ApplyMap - script function

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here