Get the difference between the last and the first value

Fluxlang
1

Hey,
with the following Query i have created a table which groups my data by the corresponding name and my own tags for the year and month the data point was recorded.

from(bucket: “myBucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == “S” and
r.usage== “U1” and
r.point== “P1”
)
|> pivot(
rowKey:[“_time”],
columnKey: [“_field”],
valueColumn: “_value”
)
|> group(columns: [“Name”,“year”,“month”])

Now i have my values in a column named “value_int”

What i want to achive now is to get the difference between the last and the the first value.

I tried to get this done with the map function like this:
|> map(fn: (r) => ({ r with
Wert: last(columns: [value_int]) - first(columns:[value_int])
}))

But i only get an Error
image

Is there a way to get this difference?
Thank you in advance for your help

2

Hello @Patse,
Thanks for your question.
You’d have to follow this type of logic:

data = from(bucket: “myBucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == “S” and
r.usage== “U1” and
r.point== “P1”
)
|> pivot(
rowKey:[“_time”],
columnKey: [“_field”],
valueColumn: “_value”
)
|> group(columns: [“Name”,“year”,“month”])

first = data |> first() |> findRecord(fn: (key) => true, idx: 0)
last = data |> last() |> findRecord(fn: (key) => true, idx: 0)

diff = first._value - last._value 

data |> map(fn: (r) => ({ r with
Wert: diff
}))
1 Like
3

@Patse Are you hoping to do this for all (multiple) input tables? There are a couple of different ways to do it:

Option 1

  1. Define two streams, each with a unique selector (first() and last())
  2. Union the streams together. As long as the group keys of the two streams are the same, the first and last rows should get grouped together.
  3. Run difference() on the union’d streams.
data = () =>
    from(bucket: "myBucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r._measurement == "S" and r.usage == "U1" and r.point == "P1")

first = data() |> first()
last = data() |> last()

union(tables: [first, last])
    |> difference()

Option 2

  1. Create a custom aggregate with reduce().
  2. Drop unnecessary columns added by the reduce operation.
from(bucket: "myBucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "S" and r.usage == "U1" and r.point == "P1")
    |> reduce(
        identity: {index: 0, first: 0.0, _value: 0.0},
        fn: (r, accumulator) => ({
            index: accumulator.index + 1,
            first: if accumulator.index == 0 then r._value else accumulator.first,
            _value: r._value - accumulator.first,
        })
    )
    |> drop(columns: ["index", "first"])
2 Likes