Using a lookup table in a calculation
Available from version 8.3 and later.
A calculation or a calculated event property reads a lookup table through an input, the same way it reads a measurement. In the script, the input's alias has a query function that returns the matching rows.
To save or run a script, you need the READ_LOOKUP_TABLES privilege.
Which calculations can use a lookup table
Calculation type | Lookup table inputs |
|---|---|
Raw | Allowed, next to exactly one input that is not a lookup table. |
Sampled | Allowed, next to at least one input that is not a lookup table. |
Simulated | Lookup tables are the only inputs a simulated calculation can have. |
Calculated event property | Allowed. |
The same rules apply to calculations in measurement prototypes.
Add a lookup table input
- Open the calculation or the event property configuration and click Edit.
- In the inputs table, click the plus button. The Add data reference window opens.
- Enter an Alias, for example _Limits. You use it in the script.
- Set Data reference type to LookupTable, and select the table under Lookup table.
- Confirm the input, then save the calculation.
A lookup table input has no aggregation. Once saved, the calculation is listed under Referenced by on the lookup table. Deleting the table also deletes every resource that depends on it, directly or through another resource, such as this calculation.
Query the table in the script
rows := _Limits.query(columns, filter)
rows := _Limits.query(columns, filter, params)- columns is an array of column names each result row carries. Pass [] for every column.
- filter is a boolean expression evaluated for every row, with the row's columns as variables, for example "Active == true && Weight > 50". Pass "" for every row.
- params is an optional map of values the filter reads as params.<name>.
query always returns an array of maps, one per matching row. No matching row returns an empty array, not an error. Naming a column that does not exist, or a filter that does not compile or does not return a boolean, is an error.
A column whose name is not a plain word (for example one with a space) is not available as a variable in the filter. Read it through row instead: row['Order Weight'] > 50.
Example: look up a value for the current input
_A is a measurement with a product code, _Thresholds is a lookup table with the columns ProductCode and MaxTemperature.
rows := _Thresholds.query(["MaxTemperature"], "ProductCode == params.code", {
code: _A.Value
})
if len(rows) == 0 {
return undefined // no row for this product code
}
return rows[0].MaxTemperatureUse params to pass input values into the filter, rather than building the filter string from them.
Example: read a table on a schedule
A simulated calculation runs on its own interval and only reads lookup tables.
rows := _Table.query(["Status"], "Active == true && row['Order Weight'] > 50")
if len(rows) == 0 {
return undefined
}
return rows[0].StatusBoth examples are also available as example scripts in the calculation editor.
Things to know
- A lookup table holds only its current rows. Every run of a calculation, including a recalculation of a past period, reads the rows as they are at that moment.
- A raw calculation needs exactly one input that is not a lookup table. The editor does not let you remove it.
- A sampled calculation needs at least one input that is not a lookup table. The editor does not let you remove the last one.