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.
Important

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:

ParameterDescription
result lookup fieldThe field value to be returned
primary key field NThe 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 NThe 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 valueOptional. The default value the function returns if a match to the primary key field is not found.
Important

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
Note

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

  • In the Navigation bar, select Data.
  • In the Action bar, select + NewAdd 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 + NewCreate 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 LoadLoad nowFull.

Create a dashboard insight

  • In the Navigation bar, select the Content tab, and then select + NewAdd 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.

Note

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 Filters & Prompts.
  • In the Dashboard Filters Manager, select Presentation Variables.
  • From the Data panel, drag and drop New Variable to the presentation variables tray.
  • For a given presentation variable pill, specify the following properties:
ParameterDescription
Display Name:Currency Code
Variablepv_Currency_Code
Data Typestring
default valueUSD
Fieldtbl_CurrencyRateDailyAverage.Cur_Id
  • Select Done.
  • In the Action bar, 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.
  • 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.