| Expand | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||
|
...
Query lookup tables are shown along with uploaded lookup tables, in Data search → Lookup management. The query lookup will be ready when the indicator in the Status field turns green. The Type field shows Upload, Static Query or Dynamic Query to indicate the lookup table type. Learn more in Manage and edit lookup tables.
...
Both static and dynamic query lookups can be created as a time range lookup. When using this option, the same entry of your key field will be matched with different results depending on the corresponding time range between the dates specified in a timestamp field 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 field dropdown menu will appear, and you must choose the timestamp-type field you want to use among the ones added into the field on the left.
...
| Rw ui textbox macro | ||
|---|---|---|
| ||
Choosing your timestamp field If the type of the field is not timestamp-type, it will not appear in the Time range field dropdown. Remember that you can check the type of field by clicking the icon that appears when hovering over a field header. You can use the Timestamp (timestamp) operation to transform strings representing dates or epoch times in milliseconds (integers) into timestamp data type.  |
Time range lookup example
For example, imagine you have a query that shows the cities to which a user (Mike) has been calling in different time ranges.
As you can see in the picture below, Mike talked to colleagues in different cities at different times. Let’s say we want to consider the time from one call to the next as a time slot assigned to that specific city. For that purpose, we will define a time range lookup as described above.
...
After defining a time range lookup based on this data, we want to define a new field in another query to match the user with the city that corresponds to them according to the time slot mentioned before and recorded in the lookup.
As you can see in the picture below, the new field shows the corresponding city according to the time in the eventdate field. For example, you can see the value New York in events sent at 16:00 because the time range lookup matches New York to any event received from 13:00 to 17:00.
...