Create a lookup table from a query
Users with the required permissions can use the content of a query to create a lookup directly from the search window. You can create two different types of lookup tables using query data: static query lookups and dynamic query lookups.
Static query lookup
These lookup tables are created using query data from a specific period of time. You cannot create this type of lookup if you enable real-time in your query –you must always choose a period of time using the time range selector in the query toolbar. These lookups work exactly the same as uploaded lookup tables, since both contain a fixed set of data that you can use to enrich a raw data table.
The lookup will include the last events in the specified time range containing all the unique values in the key column selected.
Dynamic query lookup
These lookup tables are also created with query data, but they differ from static query lookups in that they are constantly populated with new sets of data. By default, they are updated every 5 minutes since the creation date. If a new event arrives where the key column value has different row values, the old ones will be overwritten.
If your query groups data, the dynamic query lookup will be updated according to the grouping time indicated. For example, if your query groups data every 1 hour, the lookup will be populated with a new set of data every 1 hour. Note that if the grouping period is less than 5 minutes, the lookup will be updated every 5 minutes.
You can create a dynamic query lookup with the real-time option activated, but you can also define a specific time range. If you do this, the first data set until it is updated will be the data in the time frame indicated. For example, if you set Last week as the time range, the data in the last seven days will be used to populate the lookup, and then will be populated with new data following the rules explained above.
Check the following video to start learning about query lookups:
Create a query lookup
To define a lookup table using the content of a query, follow these steps:
Note that this feature is only enabled in certain domains by default. If you need to use it and is not enabled in your domain, contact the Devo support team.
- Go to Data Search and access the required table.
- Choose the required period of time using the time range selector and click Apply Interval.
- In the data table toolbar, click the gear icon and then select Table Operations → New Query Lookup.
The New Query Lookup window appears. Enter the required information.
Name Give a name to the lookup table. Data columns Drag to this field the data columns you want to add to the lookup table. Mode Choose either Static query or Dynamic query. Key column Select one of the columns added as key column. Learn more about this here.
Click Save settings. You will receive a notification when the file is uploaded.
Query lookup tables are stored along with uploaded lookup tables, in Data Search → Lookup Management. The query lookup will be ready when the indicator in the Status column turns green. The Type column shows Upload, Static Query or Dynamic Query to indicate the lookup table type. Learn more in Manage and edit lookup tables.