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.

Signature

Copy
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:

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.

Copy
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

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

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

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:

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