Combine two measurements and perform calculation

Fluxlang
1

I have two measurements and would like to combine the values from both to perform an additional calculation (Total = PowerIn + pv_wirkleistung - PowerOut). Unfortunately I do not know how to do this. Could someone maybe help me?

This is how the data looks like:

|_field          |_measurement |_time                           |_value  |
|pv_wirkleistung |pv_inverter  |2020-12-27T11:15:20Z            |160     |

|_field         |_measurement      |_time                           |_value  |
|PowerIn        |electricity_meter |2020-12-27T11:15:20.279593818Z  |380.89  |
|PowerOut       |electricity_meter |2020-12-27T11:15:20.279593818Z  |0       |

and these are the queries I would like to combine:

data1 = from(bucket: "telegraf")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "electricity_meter")
|> filter(fn: (r) => r._field == "PowerIn" or r._field == "PowerOut")
|> yield(name: "data1")

data2 = from(bucket: "telegraf")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "pv_inverter")
|> filter(fn: (r) => r._field == "pv_wirkleistung")
|> filter(fn: (r) => r._value < 10000)
|> toFloat()
|> yield(name: "data2")

I would be happy about a solution.

1 Like
2

+1 on writing such a good question.

I’ve found that the current GUI is a bit of a challenge with advanced queries like this that combine several expressions into one. That said, I don’t think you need the yield lines since you’re already assigning each statement into data1 and data2 variables. Unless you know for sure the temp variables won’t work without them.

Doing math between temp data variables however isn’t something I’ve done yet.

From a quick browse through the docs, it was mentioned that operands for a math expression needed to be in the same row.

(Take with grain of salt) if true, then you’ll need some way to join the input/output values with the values in the inverter set. Timestamp might work, but they have difference precision - is that expected? If yes and you’re only sampleing every second, you could truncate the input/output timestamps to nearest whole second and use that.

Take a look at the sql examples at the very bottom of this page - the join operation is likely very similar to what you need.

Finally , There’s quite likely serval different ways to do this too. Let us know how you go.

2 Likes
3

I think you might find useful the function join join
Something like this should work

   join(
   tables: {data1:data1, data2:data2},
   on: ["_time"]
  )
1 Like
4

Thanks for pointing me in the right direction. One problem was indeed the different timestamps. So I did:

|> aggregateWindow(every: 5s, fn:mean)

Then as both of you suggested:

join(tables:{data1:data1, data2:data2}, on:["_time"])
|> map(fn:(r) => ({r with _calc: r._value_data1 + r._value_data2}))

That gives me two tables. One for “PowerIn” one for “PowerOut”. But it works and now I just have to adjust the calculation.