Pivot - Is there any way to add a column with a row index

Fluxlang
1

from(bucket: “telegraf”)
|> range(start: -30m)
|> filter(fn: ® => r._measurement == “mem”)
|> filter(fn: ® => r._field == “used”)
|> aggregateWindow(every: 10m, fn: max)
|> tail(n: 3, offset: 1)
|> pivot(rowKey:[“host”], columnKey: ["_field", “_time”], valueColumn: “_value”)

I’m trying to do some comparisons of memory over time. I can’t work with the timestamp in the column name though. I was trying to add a column with the row index so i could pivot on that instead of time. Any ideas?

#group FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE
#datatype string long dateTime:RFC3339 dateTime:RFC3339 string string long long long
#default _result
result table _start _stop _measurement host used_2019-11-09T09:00:00.000000000Z used_2019-11-09T09:10:00.000000000Z used_2019-11-11T22:10:00.000000000Z
0 2019-11-08T22:10:17.534303843Z 2019-11-11T22:10:17.534303843Z mem host1 918052864 917450752 846524416
1 2019-11-08T22:10:17.534303843Z 2019-11-11T22:10:17.534303843Z mem host2 7565942784 7548379136 6653722624

Thanks

2

@eastwood, what’s the exact comparison you’re trying to do? Something like current memory usage vs memory usage 30 minutes ago?

3

Yes, and maybe calculate a rate of growth… More than anything i’m getting used to the language and I was exploring options other than joins.

4

Hello and weclome @eastwood,
Thanks for your question!
To add a column with the row index try adding:

  |> map(fn: (r) => ({_time: r._time, index: 1}))
  |> cumulativeSum(columns: ["index"])
  |> yield(name: "row_index")
5

that worked, thanks!

1 Like
6

@Anaisdg this is a nice little trick, I have a situation where two or more rows in the same “table” means something and I couldn’t figure out how to return only those tables untul I came across this post:

  |> map(fn: (r) => ({r with index: 1}))
  |> cumulativeSum(columns: ["index"])
  |> filter(fn: (r) => r["index"] >= 2)

is exactly what I needed!

1 Like
7

@thopewell I’m happy it helped!