# Difference between two streams

**URL:** <https://community.influxdata.com/t/difference-between-two-streams/22324>\
**Category:** InfluxDB 2\
**Created:** [October 29, 2021, 3:34pm UTC](https://community.influxdata.com/t/difference-between-two-streams/22324 "2021-10-29T15:34:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ced2flux](https://avatars.discourse-cdn.com/v4/letter/c/58956e/32.png) [@ced2flux](https://community.influxdata.com/u/ced2flux)\
**Post date:** [October 29, 2021, 3:34pm UTC](https://community.influxdata.com/t/difference-between-two-streams/22324/1 "2021-10-29T15:34:40Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![ced2flux](https://avatars.discourse-cdn.com/v4/letter/c/58956e/32.png) [@ced2flux](https://community.influxdata.com/u/ced2flux)\
**Post date:** [November 14, 2021, 8:09pm UTC](https://community.influxdata.com/t/difference-between-two-streams/22324/2 "2021-11-14T20:09:13Z")

</div>

Up…

Any ideas how to solve this ?

thanks

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [November 15, 2021, 6:01pm UTC](https://community.influxdata.com/t/difference-between-two-streams/22324/3 "2021-11-15T18:01:03Z")

</div>

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”

> **[experimental.join() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/experimental/join/)**
>
> experimental.join() joins two streams of tables on the group key and \_time column.

```auto
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():

> **[join() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/join/)**
>
> join() merges two streams of tables into a single output stream based on columns with equal values. Null values are not considered equal when comparing column values. The resulting schema is the union of the input schemas. The resulting group key is...

> **[map() function | Flux 0.x Documentation](https://docs.influxdata.com/flux/v0.x/stdlib/universe/map/)**
>
> map() iterates over and applies a function to input rows.

```auto
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