Versions Compared

Old Version 40

changes.mady.by.user Borja Moro Moreno

Saved on

compared with

New Version 41

changes.mady.by.user Borja Moro Moreno

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel2
typeflat

About lookup correlation

You can run a search and correlate its content with values from a lookup table. If any value in the key field of the lookup table is also present in your query, the corresponding values of the selected lookup field will be added to a new field.

You also have the option to consolidate all the values from a lookup row into a single field. This can be helpful for checking the values contained in the lookup (including the values of the key field) or when using a lookup that contains only the key field (to seek correlation without enrichment).

What permissions do I need?

To be able to view and use lookup operations when working in the search window, you need to have at least the View version of the Lookups permissions for upload lookups or the Query lookups permission for query lookups. Having the User resources permission is equivalent to having the Manage version of these two permissions.

These two permissions are hierarchy-based, meaning you need to have Lookups assigned in order to be able to have Query lookups. Notice at least the View version of the Finders permission is required to enable the Lookup permission (know more about permissions here).

Additionally, if you’re not the owner of the lookup, you need to receive at least View access to the lookup in question via sharing, otherwise, you will not be able to see it. Check Share lookups to know more.

Using lookup operations in the search window

You can use the search window interface to perform the value correlation and the creation of new fields to enrich the data table.

  1. Go to Data search and open a data table.

  2. Click Create field in the query toolbar and give it a meaningful name.

  3. Select Lookups in the operation category

  4. Choose the desired operation based on the type of lookups and the enrichment you need:

  5. Add the required arguments for the selected operation.

    • Lookup name: choose the lookup you want to use to enrich your table by using the dropdown, which shows all the lookups you have access to.

    • Lookup field: choose the lookup field you want to use to enrich your table by using the dropdown, which shows all the fields within the selected lookup except the key field.

    • Key: choose the table field you want to use to find matches with the lookup key field (their data types must match to enable the field creation and the enrichment will be successful only when their values match). You can use the dropdown, which shows all the fields within your table, but you can also manually write the field name using the pencil icon.

    • Time (optional - only for lookups that keep history): choose the table timestamp you want to use to correlate with the lookup timestamp (the data type must be a timestamp to enable the field creation). You can use the dropdown, which shows all the fields within your table, but you can also manually write the field name using the pencil icon.

  6. Click Create field and the table will be enriched with a new field containing lookup values wherever successful matches are found and null values when they are not.

20_add looking values to your query.png

Using lookup operations in LINQ

You can also correlate lookup values and create new fields by using the corresponding LINQ syntax. To do this, you can either use the Free Text Query to open the search or open the Query code editor if you have it already open.

Available operations

Syntax overview

Use the operator select... as...  and add the operation syntax. The syntax and arguments depend on the operation:

Operations

Syntax

Arguments

Lookup (lu)

lu(...)

(

Status
title"lookup_name"
,
Status
colourPurple
title"lookup_field"
,
Status
colourBlue
titlekey_field
)

Lookup (hlut)

hlut(...)

(

Status
title"lookup_name"
,
Status
colourPurple
title"lookup_field"
,
Status
colourBlue
titlekey_field
)

(

Status
title"lookup_name"
,
Status
colourPurple
title"lookup_field"
,
Status
colourBlue
titlekey_field
,
Status
colourGreen
titletimestamp_field
)

Lookup (hlurjson)

hlurjson(...)

(

Status
titlelookup_name
,
Status
colourBlue
titlekey_field
,
Status
colourGreen
titletimestamp_field
)

Info

Syntax considerations

  • Status
    title"lookup_name"
    → This must be the name of lookup that contains the data you want to use to enrich your data.

  • Status
    colourPurple
    title"lookup_field"
    → This must be the lookup field you want to use to enrich your data, which must not be the same as the lookup key field.

  • Status
    colourBlue
    titlekey_field
    → This must be the table field that will be used to find matches with the lookup key. The name can be different than the lookup key field as long as the data type coincide and the values it contains are potential matches (username-user). The absence of matches will return null and a different data type will return an error when running the query.

  • Status
    colourGreen
    titletimestamp_field
    → This must be the table timestamp that will be used to correlate with the lookup timestamp. A data type other than timestamp will return an error when running the query.

One-field lookups

If your lookup is composed only of the key field because you are interested only in the correlation but not the enrichment, you need to use the operation Lookup (hlurjson).

As an alternative, you can use lookups with two fields; the key field and a field with default values such as true. You can use the Lookup (lu) operation to enrich your data with the default values that indicate only correlation.