Patse
November 30, 2022, 9:24am
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

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

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

scott
December 9, 2022, 6:25pm
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
Define two streams, each with a unique selector (`first()`

and `last()`

)
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.
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
Create a custom aggregate with `reduce()`

.
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