Error in query: calculating with map function


I’m get errors with the query below and can’t seem to find why. I want to query the data of 4 sensors and perform the calculation below for each timestamp. The expected query output is the calculated value for each timestamp.

The series (measurement,tagpairs & fieldkey) of my 4 sensors look like this:
Sensor01: eanmeters,Locatie=None,Meter_ID=AP1_KWE_P_Afname,Sensor_ID=sensor_ID,Unit=kW 541448860020077192
Sensor02: eanmeters,Locatie=None,Meter_ID=AP2_KWE_P_Injectie,Sensor_ID=sensor_ID,Unit=kW 541448860015740971
Sensor03: eanmeters,Locatie=None,Meter_ID=AP4_KWE_P_Productie_WKK,Sensor_ID=sensor_ID,Unit=kW 541454897100135658
Sensor04: eanmeters,Locatie=None,Meter_ID=AP4_KWE_P_Productie_PV,Sensor_ID=sensor_ID,Unit=kW 541454897100135672

My query so far:

from(bucket: "data")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "eanmeters")
  |> filter(fn: (r) => r["Meter_ID"] == "AP1_KWE_P_Afname" or r["Meter_ID"] == "AP2_KWE_P_Injectie" or r["Meter_ID"] == "AP4_KWE_P_Productie_PV" or r["Meter_ID"] == "AP4_KWE_P_Productie_WKK")
  |> aggregateWindow(every: 1h, fn: last, createEmpty: false)
  |> group(columns: ["_measurement"])

No issues here: it filters out the values and creates a single table containing all 4 sensors
Now I want to do the calculation: sensor01 + sensor02 + sensor03 - sensor04

|> map(fn: (r) => ({_time: r._time, _value: r.AP1_KWE_P_Afname + r.AP4_KWE_P_Productie_PV + r.AP4_KWE_P_Productie_WKK - r.AP2_KWE_P_Injectie}))

This throws an error:
runtime error @7:6-7:146: map: cannot compile @ 7:14-7:145: unsupported binary expression invalid + invalid

How do I fix this?

Many thanks!!!


You can do as below


Hello @SamR,
Were you able to solve the problem?
I believe you might need to pivot first. But you’ll want to pivot on “Meter_ID” instead of “_field”.
The following worked for me:

from(bucket: "anais")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")  
  |> map(fn: (r) => ({_time: r._time, _value: r.field1 + r.field2 + r.field3 - r.field4}))
  |> yield(name: "mean")