Add lookup values to your query
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, 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
demo.ecommerce.datatable. - Click Create column in the query toolbar.
- 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).
- Add the clientIpAddress column as an argument and click Create column.
- 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).
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 Editor and use the following syntaxis:
select `lu/lookupName`(field) as newColumnName
Add lookup values in LINQ and using Activeboards or the API
You can also add lookup values to your query editing the LINQ syntax directly. To do it, open the Query Editor in the search window and use the following syntax:
select `lu/lookupName/lookupColumn`(field) as `newColumnName` |
For example:
from demo.ecommerce.dataselect `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address` |
To add lookup values to an Activeboard or while using the Devo API, you must use the following syntax:
select lu("domainName", "lookupName", "lookupColumn", field) as `newColumnName` |
For example:
from demo.ecommerce.dataselect lu("DOMAIN", "IP_list", "StreetAddress", clientIpAddress) as `IP street address` |
