Add lookup values to your query
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 view permissions on Lookups for upload lookups or Query lookups for query lookups.
Take into account that these two permissions are hierarchy-based, meaning you need to have Lookups assigned in order to be able to have Query lookups.
Additionally, you need to receive at least View access to the lookup in question via sharing. Check Share lookups to know more.
Using lookup operations in the search window
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.
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:
- Go to Data Search and open the
- Click Create column in the query toolbar and give it a meaningful name (in this case, IP street address).
Select custom to show only lookup operations, look for the name of the lookup (in this case IP_list) and select the StreetAddress operation.
Add the clientIpAddress column as an argument and click Create column.
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.
- Repeat the process to add the printer brand column but in this case, select the Brand lookup operation and then choose again the clientIpAddress column as an argument (you can name it IP Brand).
Using lookup operations in LINQ
You can also add lookup values by using the corresponding LINQ syntax in the query. To do it, you can either use the Free Text Query to open the search from the beginning or open the Query code editor if you have it already open. This is the syntax you need to use:
select `lu/lookupName/lookupColumn`(field) as newColumnName
This is the required LINQ query to add the lookup in the example shown above using the search window interface:
select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`
Lookup operations result
When using a lookup operation, both the key column specified in the lookup when created and the column specified as an argument in the operation are searched for matching values. When they are found, the corresponding value of the lookup column selected as the operation will be added to the new column.
In the example above, both columns are created with the lookup values, 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.
In the search window
To do it, follow the steps in the process explained above but the operation selected must be Whole entry of... instead.
You can also add lookup rows as JSON objects by using the corresponding LINQ syntax in the query.
To do it, you can either use the Free Text Query to open the search from the beginning or open the Query code editor if you have it already open. This is the syntax you need to use:
select `lu/lookupName`(field) as newColumnName
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.
Apart from showing how to create lookups, the following videos 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.
Create a query lookup
Time range lookups