AnalyzeData elements

Add columns through Lookup

View as Markdown

A Lookup is an action that allows you to connect related columns between two data elements, bringing the data from one element to another. The two elements are joined by a pair of columns — one from each element — that share common values. These joining columns are join keys.

To create a lookup column with a formula, add a calculated column and use the Lookup function. You can also add a lookup column using the Add Lookup modal, which is described below.

Requirements

  • To create a lookup column, you must have Can Edit or Can Explore access on the workbook.

Notes

  • Using the Lookup function or the Add Lookup modal, you can perform a “self-lookup” - a lookup where both the related key and local key are in the same data element.c
  • When performing a lookup between two data elements, the following requirements apply:
    • Both elements must be in the same workbook
    • Both elements must be sourced from the same connection
    • The element you are joining to cannot be a child of the element you are joining from

Add a column through Lookup

To add a lookup column:

  1. Open the workbook for editing.
  2. Select the data element that you want to add a column to.
  3. Next to any column’s name, click the caret icon to open the column menu.
  4. Select Add column via > Lookup. The Add Lookup modal opens.
    • The first section, Which column would you like to add?, prompts you to select:
      • A source element
      • A column, from the source element, to use in your new column
      • [optional] An aggregate value to apply to the column
    • The second section, Map two elements, prompts you to define one or more sets of join keys.
  5. Under Select element, select your source element. Elements are organized by their workbook pages.
  6. Under Column(s) to add, select the column you want to use in your new column.
  7. [optional] Under Aggregate, select an aggregate function to apply to the column’s values. Applying an aggregate function turns the Lookup into a Rollup.
  8. Under Map two elements, select a column from each data element to use as a join key.
  9. [optional] To add additional pairs of join keys, click +Add another mapping and repeat step 8.
  10. [optional] To match rows where the join keys on both sides are null, select Match null values. By default, a null value never matches another null value, so rows with a null join key return no match. Selecting this option creates the column with the LookupMatchNulls function instead of the Lookup function.
  11. Click Done.