Rename value (using pivot->rename->un-pivot)

Fluxlang
1

Is it possible to make un-pivot with Flux ?

The main reason is to rename value in particular column. I am able to make pivot, rename value but not able to unpivot back so I can display renamed value rather than original one.

Another approach could be to have a function to rename value(s) if possible rather than just column.

See my example below trying to rename value but failing to making unpivot…

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
    r._measurement == "fautest" and
    r.trigger =~ /interval/ and
    r._field =~ /green/)
|> group(columns: ["_time", "_value", "trigger", "msgid"], mode: "except")
|> sum(column: "_value")
|> map(fn:(r) => ({
     r with
     _field: r._field,
     _value: r._value * 30}))
|> pivot(rowKey:["_start"], columnKey: ["_field"], valueColumn: "_value")
|> rename(columns: {green: "GREENXXX"})


,result,table,_measurement,_start,_stop,GREENXXX
,,0,fautest,2020-01-22T12:58:11.333219107Z,2020-01-24T12:58:11.333219107Z,27810
2

@salvq Flux doesn’t support un-pivoting data yet. I’m not sure I fully understand what you’re trying to do. Could you provide some example input and an example of your desired output?

3

@scott Let me explain the reason behinds that first…

For some graph and legends, Grafana use value(s) from _field column to show legend description etc. Current values in some of the tables are not the ones I want to show or see in my graphs.

When using below script the result…

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
    r._measurement == "fautest" and
    r.trigger =~ /interval/ and
    r._field =~ /green/)
|> group(columns: ["_time", "_value", "trigger", "msgid"], mode: "except")
|> sum(column: "_value")
|> map(fn:(r) => ({
     r with
     _field: r._field,
     _value: r._value * 30}))

,result,table,_field,_measurement,_start,_stop,_value
,,0,green,fautest,2020-01-20T23:00:00Z,2020-01-24T18:03:56.868145027Z,33900

I need to change status green to Running or similar, so this was my expectation. Therefore I use pivot to be able to apply rename() function to columnKey and then return table to before pivot. If there is any other way how to manage this I would really appreciate I just cannot change whole downstream process and rename status.

Expected result from script

,result,table,_field,_measurement,_start,_stop,_value
,,0,RUNNING,fautest,2020-01-20T23:00:00Z,2020-01-24T18:03:56.868145027Z,33900
4

You can do that in the map() call. Is green the only value you expect, or are there others?

5

@scott yes it is the only value.

How to apply map to change values ?

Thanks

6

@salvq

|> map(fn:(r) => ({
     r with
     _field: "RUNNING",
     _value: r._value * 30
  }))

You could even layer in a little conditional logic into the map() function to reassign values in the the _field column based on their current value. For example, if you had the following _fields:

  • green
  • grey
  • red

You could:

|> map(fn:(r) => ({
     r with
     _field: if r._field == "green" then "RUNNING"
        else if r._field == "grey" then "SLEEPING"
        else if r._field == "red" then "STOPPED"
        else "unknown",
     _value: r._value * 30
  }))
7

…so easy and working :slight_smile:

Thank you