Document toolboxDocument toolbox

Add lookup values to your query

Owned by Juan Tomás Alonso Nieto (Unlicensed)
Last updated: May 18, 2021
5 min read

Overview

You can run a search and correlate its content with values from a lookup table. If any value in the key column of the lookup table is also present in your query, the values in those lookup rows can be added in a new column.

The following videos, apart from showing how to create lookups, show how to use their values in another query. Take into account that the results may vary significantly from regular lookups to time range lookups so make sure to check these videos for extra help before exploring this article.


Following the previous example shown in the Upload a lookup table article, suppose you want to include two lookups in the demo.ecommerce.data table showing the street address and printer brand corresponding to the IPs in the data table. To do it:

  1. Go to Data Search and open the demo.ecommerce.data table.
  2. Click Create column in the query toolbar. 

  3. Select custom and look for the name of the lookup (IP_list). Select the StreetAddress column. Give the column a meaningful name (in this case, IP street address).

    Note that it is not possible to add a lookup to your query after grouping data. The custom button will not appear in the window.

  4. Add the clientIpAddress column as an argument and click Create column.

  5. Repeat the process to add the printer brand column. In this case, add the Brand column of the lookup and choose again the clientIpAddress data table column as argument (you can name it IP Brand).

Add lookup values in the LINQ query editor

You can also add lookup values by editing the LINQ query directly. To do it, open the Query code editor and use the following syntaxis:

select `lu/lookupName/lookupColumn`(field) as newColumnName

This is the required LINQ query to add the lookup in the example above:

from demo.ecommerce.data
select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Both lookup values created, showing the street and printer brand corresponding to the IPs of the clientIpAddress column that are also in the key column of the lookup table. These columns will show null for those IPs that are not included in the lookup table.

Add all the values in a row

When a match is found between the content of your query and a value in your lookup key column, you can select the Whole entry of... operation to add all the values in that lookup row as a JSON object in a new column.

To do it, follow the steps in the process explained above but instead of selecting one of the lookup columns, select the Whole entry of... operation that appears in the list.

You can split the resulting JSON objects into their different key/value pairs and transform them into different data types using the operations in the JSON group.

Add lookup rows in the LINQ query editor

You can also add lookup rows as JSON objects editing the LINQ query directly. To do it, open the Query code editor and use the following syntaxis:

select `lu/lookupName`(field) as newColumnName

You can run a search and correlate its content with values from a lookup table. If any value in the key column of the lookup table is also present in your query, the values in those lookup rows can be added in a new column.

The following videos, apart from showing how to create lookups, show how to use their values in another query. Take into account that the results may vary significantly from regular lookups to time range lookups so make sure to check these videos for extra help before exploring this article.

Following the previous example shown in the Upload a lookup table article, suppose you want to include two lookups in the demo.ecommerce.data table showing the street address and printer brand corresponding to the IPs in the data table. To do it:

  1. Go to Data Search and open the demo.ecommerce.data table.
  2. Click Create column in the query toolbar. 
  3. Select custom and look for the name of the lookup (IP_list). Select the StreetAddress column. Give the column a meaningful name (in this case, IP street address).

  4. Add the clientIpAddress column as an argument and click Create column.

  5. Repeat the process to add the printer brand column. In this case, add the Brand column of the lookup and choose again the clientIpAddress data table column as argument (you can name it IP Brand).

Add lookup values in the LINQ query editor

You can also add lookup values by editing the LINQ query directly. To do it, open the Query code editor and use the following syntaxis:

select `lu/lookupName/lookupColumn`(field) as newColumnName

This is the required LINQ query to add the lookup in the example above:

from demo.ecommerce.data
select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Both lookup values created, showing the street and printer brand corresponding to the IPs of the clientIpAddress column that are also in the key column of the lookup table. These columns will show null for those IPs that are not included in the lookup table.

Add all the values in a row

When a match is found between the content of your query and a value in your lookup key column, you can select the Whole entry of... operation to add all the values in that lookup row as a JSON object in a new column.

To do it, follow the steps in the process explained above but instead of selecting one of the lookup columns, select the Whole entry of... operation that appears in the list.

You can split the resulting JSON objects into their different key/value pairs and transform them into different data types using the operations in the JSON group.

Add lookup rows in the LINQ query editor

You can also add lookup rows as JSON objects editing the LINQ query directly. To do it, open the Query code editor and use the following syntaxis:

select `lu/lookupName`(field) as newColumnName