Including more than one field when pivoting

1

hello

in the following flux query, I want to return

_time, Longitude,Latitude,Flag


from(bucket: "PSK")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._field == "Longitude" or r._field == "Latitude" or r._field == "Flag")  
  |> filter(fn: (r) => r["Mode"] == "FT8")
  |> keep(columns: ["_time", "_value", "_field", "Flag"])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

This does return those columns but in grafana it groups them by Flag. WOnder if the issue is dataframes in grafana

image
image1316×272 12.9 KB

THanks

2

@yosiasz Is Flag a field or a tag. I can’t tell from your query.

The following would indicate that it’s a field:

|> filter(fn: (r) => r._field == "Longitude" or r._field == "Latitude" or r._field == "Flag")

But this indicates it’s a tag:

|> keep(columns: ["_time", "_value", "_field", "Flag"])

In the Flux data structure, fields are represented by two columns: _field (the field key) and _value (the field value). Tags are represented by a single column where the column name is the tag key and the column value is the tag value.

So if Flag is a tag, it’s 1) not affected by the pivot and 2) is, by default, part of the group key which explains why Grafana is using it to group data.

1 Like
3

this is the line protocol

M1CVZ,Band=20m,Continent=EU,Country=Sweden,Flag=🇸🇪,Mode=FT8,topic=pskr/filter/v2/20m/FT8/M1CVZ/SM2GHI/IO91/KP25/223/284 RxCC="284",TxCC="223",Maidenhead="KP25ax",Call="SM2GHI",Signal=-13,Sequence="4.7309249901e+10",Frequency=14075941,Latitude=65.95833333333333,Longitude=24 1718040720000000000
M1CVZ,Band=20m,Continent=EU,Country=Norway,Flag=🇳🇴,Mode=FT8,topic=pskr/filter/v2/20m/FT8/M1CVZ/LA1PHA/IO91/JP76/223/266 RxCC="266",TxCC="223",Maidenhead="JP76bh75",Call="LA1PHA",Signal=-16,Sequence="4.7309188289e+10",Frequency=14075435,Latitude=66.3125,Longitude=14.141666666666667 1718040524000000000

Ok, in fact it does have a LOT of tags. until the field sets start at RxCC

Ok thanks

4

@yosiasz Ok, since Flag is a tag and it’s the only column in the group key left after the pivot, you can just ungroup your data:

from(bucket: "PSK")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._field == "Longitude" or r._field == "Latitude")  
  |> filter(fn: (r) => r["Mode"] == "FT8")
  |> keep(columns: ["_time", "_value", "_field", "Flag"])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> group()
1 Like
5

Thanks so much, that was it!