Hi everyone,
I am trying to add an additional column to my queried data with a fixed value equal to the maximum value of the column “_value”.
The query so far looks like this (not working):
|> map(
fn: (r) => ({
_time: r._time,
_measurement: r._measurement,
maxvalue: max(columns: [“r._value”])
}),
)
Alternative I tried it with fill() but also without success:
|> fill(column: " maxvalue “, value: max(columns: [”_value"])
Can someone please help me out to find the right solution?
I messed around with this yesterday but could not get it. I am sure it’s possible (and probably easy once I see how it’s done). Post here if you get a solution and I will do the same.
Hey,
I’m pretty sure it’s not the most elegant way to do it but I found the following workaround.
b1 = from(bucket: "${Bucket}")
//apply filters and aggregate
...
|> sort(columns: ["_value"],desc: true)
|> fill(column: "number", value: float(v:1))
|> cumulativeSum(columns: ["number"])
b2 = b1
|> last(column: "number")
|> rename(columns: {number: "amount"})
|> drop(columns:["_time", "_value"])
union(tables: [b1, b2])
|> sort(columns: ["number"], desc: false)
|> fill(column: "amount", usePrevious: true)
|> filter(fn: (r) => r["number"] > 0)
|> sort(columns: ["number"],desc: true)
The trick is to build a new table with only the maximum value (last value of sorted values). Then rename the column and drop the other columns. If you join the tables by using union a new column is created. I didn’t manage to fill the whole column with this one value. Therefore, I have sorted the table in ascending order which puts the row with the value in the separate column (amount) on top. Then you can use the command fill with useprevious. That leads to a column with your maximum value.
I pretty much came up with the same thing as @ausgefluxt
Using some temperature data that I had over a 2-day range:
this is how I got the max value of the series to be displayed in its own column:
Max = from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
|> max()
|> set(key: "newValue", value: "max_value_of_the_dataset")
|> yield(name: "max_single_value")
Range = from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
|> set(key: "newValue", value: "dataset_value")
|> yield(name: "dataset_range")
union(tables: [Max, Range])
|> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
|> fill(column: "max_value_of_the_dataset", usePrevious: true)
Snippet of the final union table:
