Built-in Functions → lookup
lookup() searches a table for the primary key field(s) you provide, and returns the value of the result lookup field you specify when a match to the primary key value(s) is found. You can find this function in the Miscellaneous category of the Formula Builder.
The lookup() function is typically used to return data from a table where there is not an existing Incorta Join. The result is a direct, or inner, join that can enrich the data displayed in an insight. The lookup table must contain a key column.
Following are the development applications in which the lookup function may be used:
- As a formula column in a business schema view or in an insight that is executed at run time.
- As a presentation variable to capture user input for a lookup.
- As an insight filter.
When you use the lookup() function in a business schema view, it is necessary to test the performance of record sets greater than 10,000.
Signature
lookup(result lookup field, primary key field 1, primary key value 1, primary key field 2, primary key value 2, ....., default value)
The following table illustrates the lookup() function parameters:
Parameter | Description |
---|---|
result lookup field | The field value to be returned |
primary key field N | The table primary key fields. There can be more than one primary key field in the case of a composite key. Each primary key field must have a corresponding primary key value. |
primary key value N | The table primary key values. There can be more than one primary key value in the case of a composite key. Each primary key value must have a corresponding primary key field. |
default value | Optional. The default value the function returns if a match to the primary key field is not found. |
The result lookup field
and primary key field N
parameters must be from the same object. Starting with 5.2.9, a new validation is applied to the lookup
function that prevents saving an expression where these parameters are not from the same object. Existing expressions with a lookup
function (before upgrading to 5.2.9 and later releases) must be updated accordingly; otherwise, these expressions will return #ERROR
as a value.
Returns
The value of the result lookup field, which can be of data type date
, double
, integer
, long
, string
, text
, timestamp
, or null.
Example
Create a new insight column for sales revenue amount in Egyptian Pounds that looks up the US Dollar to Egyptian Pound exchange rate using the sales date, and multiplies it by the revenue amount in US Dollars.
lookup(sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Rate,sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Cur_ID,"EGP",sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Date,SALES.SALES.TIME_ID,1) * SALES.SALES.AMOUNT_SOLD
In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data. The Sample Data includes the SALES schema. You will also need to download the CurrencyRate_DailyAvg_201001_202004.xlsx file.
Here are the steps:
- Add the local data file source
- Create a new physical schema
- Create a dashboard insight
- Add a dashboard presentation variable
- Edit the dashboard insight
Add the local data file source
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in Data Files, select Upload Data File.
- Drag and drop CurrencyRate_DailyAvg_201001_202004.xlsx to the Upload Data File dialog.
Create a new physical schema
- In the Navigation bar, select Schema.
- In the Action bar, select + New → Create Schema.
- In Name, enter sch_CurrencyHistory, and select Save.
- In Start adding tables to your physical schema, select File System.
- In the Data Source dialog:
- For Data Source, select LocalFiles.
- For File Type, select Excel (xlsx).
- For File, select CurrencyRate_DailyAvg_201001_202004.xlsx.
- For Worksheet, select Currency_Exchange_History.
- Select Add.
- In the Table Editor, in the Table Summary section, enter tbl_CurrencyRateDailyAverage.
- Define the following key columns:
- Cur_ID
- Date
- In the Action bar, select Done.
- Perform a full load of the sch_CurrencyHistory physical schema. In the Schema Designer, in the Action bar, select Load → Load now → Full.
Create a dashboard insight
- In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
- In the Add Dashboard dialog, for Name, enter Sales Revenue, and then select Add.
- In the Action bar, select + (add icon), or select + Add Insight.
- In the Insight panel, in Tables, select Listing Table.
- In the Data panel, select Manage Dataset.
- In the Manage Data Sets panel, in Tables, select SALES and sch_CurrencyHistory. Close the panel.
- Drag and drop the following columns to the Measure tray:
- CUSTOMER_ID
- Sales Date
- Revenue
- From the Data panel, drag and drop Add Formula to the Measure tray. The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Miscellaneous Functions.
- Double-click the lookup function,
lookup(result lookup field, primary key field 1, primary key value 1, primary key field 2, primary key value 2, ....., default value)
, to add the formula to the editor. - In the Formula Editor,
- Replace result lookup field with sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Rate
- Replace primary key field 1 with sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Cur_ID
- Replace primary key value 1 with
EGP
- Replace primary key field 2 with sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Date
- Replace primary key value 2 with SALES.SALES.TIME_ID
- Delete default value
- Multiply by SALES.SALES.AMOUNT_SOLD
lookup(sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Rate, sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Cur_ID, 'EGP', sch_CurrencyHistory.tbl_CurrencyRateDailyAverage.Date, SALES.Sales Date) * SALES.SALES.AMOUNT_SOLD
- Select Validate & Save.
- In the Measure tray
- Double-click the New Formula pill and rename it to Sales Revenue in EGP.
- Double-click the Revenue pill and rename it to Sales Revenue in USD.
- Name the insight Sales Revenue by Currency Type.
- In the Action bar, select Save.
The example above illustrates the use of the lookup function in its basic form when a join does not already exist between the tbl_SALES and tbl_CurrencyRateDailyAverage tables.
You can extend the example above to allow the user to select the currency type to convert the sales revenue amount to using a presentation variable.
Add a dashboard presentation variable
- In the Action bar, select More Options (⋮ vertical ellipsis icon).
- Select Manage Dashboard Filters.
- Select Manage Dataset.
- In Tables, select SALES and sch_CurrencyHistory.
- Close the panel.
- In the Presentation Variables tray, select + Add to add a new variable.
- Specify the following presentation variable properties:
Parameter | Description |
---|---|
Column Label | Currency Code |
Variable Name | pv_Currency_Code |
Data Type | string |
Field | tbl_CurrencyRateDailyAverage.Cur_Id |
Default Value | USD |
- Select Done.
Edit the dashboard insight
- For the Sales Revenue by Currency Type insight, select Edit (pen icon).
- Double-click the Sales Revenue in EGP pill and rename it to Sales Revenue in $$pv_Currency_Code
- Select the right arrow (>) next to Sales Revenue in $$pv_Currency_Code to open the Formula Builder:
- Change primary key value 1 (
EUR
) to $pv_Currency_Code - Select Validate & Save.
- Change primary key value 1 (
- In the Action bar, select Save.
- Select a Currency Code to convert the Sales Revenue in US Dollars to, and view the results in the insight.