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.
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.
Information noteArguments 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.
Load script
Load the following data as an inline load in the data load editor to create the example below.
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 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 resulting OrderData table.
Information noteThe Lookup() function is flexible and can access any previously loaded table. However, it is slow compared with the Applymap() function.
A field contains values, loaded from a data source. At a basic level, a field corresponds to a column in a table. Fields are used to create dimensions and measures in visualizations.
Sheets are components of Qlik Sense apps. They present visualizations to app users so they can explore, analyze, and discover data. Sheets can be public or private.
