Create a lookup table from a query

Query lookup types

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:

1. Go to Data Search and access the required table.
2. Choose the required period of time using the time range selector and click Apply Interval.
3. In the data table toolbar, click the gear icon and then select Table Operations → New Query Lookup.

4. 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.
   Time range lookup	Check this option to use a timestamp type column in your table to determine the value to be matched with the key column according to the date. Learn more about time range lookups in the section below.

   Repeated values in the key column

   If the column selected as key upon the creation of a static query lookup contains repeated values, only the last value in the column will be kept, and the rest of the events with that value repeated will be discarded. In the case of a dynamic query lookup, repeated key values will be replaced by the most recent ones.

   For example, if you define a static query lookup with the following entries and select the column user as key, only the second event that contains Mike will be kept.

   

5. 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.

Time range lookups

Both static and dynamic query lookups can be created as a time range lookup. When using this option, the same entry of your key column will be matched with different results depending on the corresponding time range between the dates specified in a timestamp column of your query. This way, you can match the same value in your query with different values in your lookup, which may come in handy in different situations.

To create a time range lookup, you must check the Time range lookup checkbox in the creation process of the query lookup, as explained above. Once you do this, the Time range column dropdown menu will appear, and you must choose the timestamp-type column you want to use among the ones added into the field on the left.

For example, imagine you have the following query, which shows the cities to which a user (Mike) has been calling in different time ranges. As you can see, Mike has been talking with colleagues in Madrid from 08:00 to 13:00; with users in New York from 13:00 to 17:00; and with users in Paris from 17:00.

After defining a time range lookup based on this data, we want to define a new column in a query that contains a user column. For every Mike value in our query, we want to include the corresponding city depending on the time range specified in our lookup. As you can see in the following capture, the new column shows the corresponding city according to the time in the eventdate column. For example, you can see the value New York in events sent at 16:00, since the time range lookup said that New York corresponds to the time range that goes from 13:00 to 17:00, as explained above.

To see the creation process of a time range lookup in action, check the following video: