How to transform to categorical data influx db multiple measurements

Grafana InfluxDB
1

Hi,

I am using Grafana and Influx for visualizing electrical power consumption.

In in flux i have two measurements:

power_measurements:
{
tags: {sensor_id: ‘xyz’}
fields: {‘current’: 0.6}
}

device_metadata:
{
tags: {sensor_id: ‘xyz’}
fields: {‘off_level’: 0.1,
‘standby_level’: 0.5,
‘on_level’: 1.0}

Note that the metadata and the actual sensor measurement are in separate influx measurments. This is because the metadata can be changed by the user… and id did not want to update the existing power_measurements. In reality there are multiple different sensors with different sets of metadata.

What i would like to do is, to visualize the in grafana is the state of a sensor according to the metadata.
The state is determined by the folowing pseudo code:

if current < off_level:
    state = 0
if off_level < current < stdby_level:
    state = 1
if std_by_level < current
    state = 2

so i would like to map the field current to the ranges defined in metadata, and work with the mapped timeseries in grafana. But can’t figure out how to takle this problem (or the specific query) in Grafana.

Any help is apreciated.