[beginner] merge 2 series

InfluxDB 2
1

Hello,

I’ve just successfully imported electricity consumption into influxdb (2.0.3) and I am now trying to figure out how to visualize it in the explorer.

The hourly consumption has a “rate” tag as kwh price with values being “other” or “winterday” as it varies between day/night during winter.

I’m ending up with 2 graphs (one per rate) which I’d like to merge them in only one graph as for any given time, there is only one rate being applied.

[Album] imgur.com

Just as an illustration, here’s a screenshot from my elecricity provider (pink color => winterday rate)

[Album] imgur.com

I tried to use the “join” transform but not sure if it’s the correct approach or if I did something wrong

other = from(bucket: "bck")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "hourly_consumption")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["rate"] == "other")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

winterday = from(bucket: "bck")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "hourly_consumption")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["rate"] == "winterday")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

join(tables: {o:other, w:winterday},  on: ["_time", "_stop", "_start", "host"])
  |> map(fn: (r) => ({
  _time: r._time,
  _value: r._value_o + r._value_o
})
  )
  |> yield()

Any suggestion would be welcome :slight_smile:
Mikael

2

Seems one approach would be to remove the column splitting the values in 2 series

from(bucket: "bck")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> keep(columns: ["_time", "_value", "_measurement", "_field"])
  |> filter(fn: (r) => r["_measurement"] == "hourly_consumption")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)