Using MAP on a string column to output an Int, but then can't aggregateWindow b/c of string column

1

Hi all! I’m trying to use a string column (named InvStatus) in a map function to output an integer as a new column (called pcsavailability). I then need to do an aggregate window on the new pcsavailability column. Problem is, because InvStatus is a string, i can’t aggregate. Is there a work around for this? Thank you!

THIS WORKS:

> from(bucket: "rt-data")
>     |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
>     |> filter(fn: (r) => r["_measurement"] == "unit")
>     |> filter(fn: (r) => r["point_name"] == "InvStatus")
>     |> group(columns: ["_measurement"], mode: "by")
>     |> pivot(rowKey: ["_time"], columnKey: ["point_name"], valueColumn: "_value")
>     |> map(fn: (r) => ({ r with pcsavailability: if r.InvStatus == "RunPQ" then 1.0 else 0.0}))

THIS ERRORS OUT WITH : panic: column pcsavailability:float is not of type string

from(bucket: "rt-data")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "unit")
    |> filter(fn: (r) => r["point_name"] == "InvStatus")
    |> group(columns: ["_measurement"], mode: "by")
    |> pivot(rowKey: ["_time"], columnKey: ["point_name"], valueColumn: "_value")
    |> map(fn: (r) => ({ r with pcsavailability: if r.InvStatus == "RunPQ" then 1.0 else 0.0}))
    |> set(key: "pcsavailability", value: "_value") 
    |> aggregateWindow(every: 1d, fn: mean)
2

@Anaisdg Would love your help here please! Thanks so much.

3

Hello @Marion_Akagi,
Thanks for tagging me!
Hello,
Yes set() assigns a static column value to each row in the input tables. So you’re effectively undoing the map() function.
Instead I would select the column that you want to perform the aggWindow on with the column parameter:

Or I would use the rename() function to rename the pcsavailability to _value.

Let me know if that helps :slight_smile:

4

Thank you SO much! I don’t know if this was exactly what you meant, but learning about rename vs. set really helped. I got this to work

from(bucket: "rt-data")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "unit")
    |> filter(fn: (r) => r["point_name"] == "InvStatus")
    |> group(columns: ["_measurement"], mode: "by")
    |> pivot(rowKey: ["_time"], columnKey: ["point_name"], valueColumn: "_value")
    |> map(fn: (r) => ({ r with _value: if r.InvStatus == "RunPQ" then 1.0 else 0.0}))
    |> aggregateWindow(every: 1d, fn: mean)
    |> set(key: "point_name", value: "pcsavailability")