Hello,

I’m extracting two data streams to measure my electrical consumption and the production of my solar panels. I would like to make the difference between these two values.

Here is my current QL:

```
Pv=from(bucket: "jeedom2/autogen")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "Consommation Totale O" or r["_measurement"] == "Consommation Totale 1")
|> filter(fn: (r) => r["_field"] == "Shelly 3EM")
|> aggregateWindow(every: 1h, fn: max, createEmpty: false)
|> difference()
|> group()
|> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
|> yield(name: "Conso")
Conso=from(bucket: "pv/autogen")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "2120198970" or r["_measurement"] == "2120199320" or r["_measurement"] == "2120199323")
|> filter(fn: (r) => r["_field"] == "energy")
|> aggregateWindow(every: 1h, fn: max, createEmpty: false)
|> difference(nonNegative: true)
|> group()
|> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
|> yield(name: "Pv")
```

How do i create the difference between the Pv streams and the Conso Streams.

Thanks

Up…

Any ideas how to solve this ?

thanks

Anaisdg
November 15, 2021, 6:01pm
#3
Hello @ced2flux ,
Thanks for boosting this. Sometimes questions slip through the cracks
You would want to perform a join() and then perform a map()

In order to write the query for you though I need a little bit more information.
Are you hoping to: “Shelly 3EM” - “energy” for all measurements?
Or “Shelly 3EM”“Consommation Totale O” - “energy” “2120198970” and then some other differences for example?

I’ll assume that you want to do the former (please let me know if my assumption is wrong) “Shelly 3EM” - “energy”
https://docs.influxdata.com/flux/v0.x/stdlib/experimental/join/

```
import "experimental"
experimental.join(
left: Pv
right: Conso,
fn: (left, right) => ({
left with
Pv_value: left._value,
Conso_value: right._value,
diff: left._value - right._value
_time: left._time
})
)
|> yield(name:"diff")
```

Or you could use join and map():
https://docs.influxdata.com/flux/v0.x/stdlib/universe/join/
https://docs.influxdata.com/flux/v0.x/stdlib/universe/map/

```
join(
tables: {Pv: Pv, Conso: Conso},
on: ["_time", "_start", "_stop"],
method: "inner"
)
|> map(fn: (r) => ({diff: r._value_Pv - r._value_Conso }))
|> yield(name:"diff")
```

Please let me know if this isn’t what you’re looking for