Newby get's error in pivot function - cannot unify string with semantic.array


in my Home Assistant InfluxDB I want to figure out the heating temperatures difference. To get into Flux, I watched @Anaisdg’s introduction video, which let me to my solution approach.

But I keep running in following error, which I can’t make sense of: type error 16:6- 16:81: cannot unify string with semantic.array

Digging a bit into flux and semantic.array, I figured out it’s somehow related to the Go implementation of the Flux language and is way over my head. No idea what the message want’s to tell me.

Here is a short example data set:

My solution approach:

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "°C")
  |> filter(fn: (r) => r._field == "value")
  |> filter(fn: (r) => r.entity_id == "oil_heating_temp_return_flow_3" or r.entity_id == "oil_heating_temp_supply_flow_3")
//  |> yield(name: "supply & return flow")
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
  |> limit(n:5)
  |> yield(name: "1m min windows")
  |> pivot(rowKey: ["_time"], columnKey: ["entity_id"], valueColumn: ["_value"])
//  |> yield(name: "pivoted")
//  |> map(fn: (r) => ({ r with heat_extraction: r.oil_heating_temp_supply_flow_3 - r.oil_heating_temp_return_flow_3 }))

Removing the aggregateWindow() function and working on the original data doesn’t help as well.

Can anybody point my into a direction to solve this issue?
Thanks in advance!

Just to clarify, you want to know the difference in temperature every minute between the oil_heating_temp_return_flow_3 and oil_heating_temp_supply_flow_3 ?

Are each of the above recorded into Influx using the same timestamp?

Hi @grant1,

thanks for chiming in!

Exactly. The 1 minute interval isn’t mandatory. I just wanted to have a short enough interval to see, what happens, when one of the two sensors doesn’t deliver data in one of the periods and ensure it behaves nicely. I guess I will extend it to something longer like 5 minutes later, but have to play around a bit first.

No, they record independent from each other sadly. Hence, I did the |> aggregateWindow(every: 1m, fn: mean).
The createEmpty is more of a leftover from some tinkering around to test how it works.

The essence is, I want to have the area between the two curves roughly to see how much energy is transferred to the air in the house.

I push this a bit up, in the hope somebody might point me in the right direction. Kinda lost here.

Thanks :slight_smile:

Hi @InfluxToddler

I did a quick mock-up of your data and this query works to calculate the difference between “oil_heating_temp_return_flow_3” and “oil_heating_temp_supply_flow_3”

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "oil_heating_temp_return_flow_3" or r["entity_id"] == "oil_heating_temp_supply_flow_3")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["entity_id"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with difference: (r.oil_heating_temp_supply_flow_3 - r.oil_heating_temp_return_flow_3)}))
  |> yield(name: "test")

1 Like

Hi @grant1 !

I tested it and works fine. I have to take a closer look, what’s the actual difference later.
I am a bit time constrained now.

What pops out first ist, you used braces around the calculation for the map function.

Thank you very much!

Now only would need to measure the flow rate of the water to really get energy extracted by the radiators… :slight_smile:

But that’s another story for another day.