Problem with Map syntax

Fluxlang
1

I’m trying to do simple math on the results of a pivot and I am getting “null” values. Substituting in fixed integer or float values it works as expected, so I assume that I am not piping the values into the function, but I can’t figure out where I went wrong.

from(bucket: "IotaHourly")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["ct"] == "AC1" or r["ct"] == "AC2" or r["ct"] == "AC3" or r["ct"] == "PNL-Kitchen" or r["ct"] == "PNL-Laundry" or r["ct"] == "TeslaWC")
  |> drop(columns: ["device", "_measurement", "_start", "_stop", "_field"])
  |> pivot(rowKey: ["_time"], columnKey: ["ct"], valueColumn: "_value")
  |> map(fn: (r) => ({r with House: r._AC1 + r.AC2 + r.AC3 + r["PNL-Kitchen"] + r["PNL-Lauandry"] - r.TeslaWC }))**

Any tips (and explanation) where I went wrong? Looking at the output as a table the input numbers all look good and it is clear that the output is “null”.

Everything was taken from the Documentation Example

2

Hello @Patrick808,
I believe you’ll have to group your data before you pivot it if you want to create calculations across those different tags.

from(bucket: "IotaHourly")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["ct"] == "AC1" or r["ct"] == "AC2" or r["ct"] == "AC3" or r["ct"] == "PNL-Kitchen" or r["ct"] == "PNL-Laundry" or r["ct"] == "TeslaWC")
  |> drop(columns: ["device", "_measurement", "_start", "_stop", "_field"])
  |> group() 
  |> pivot(rowKey: ["_time"], columnKey: ["ct"], valueColumn: "_value")
  |> map(fn: (r) => ({r with House: r._AC1 + r.AC2 + r.AC3 + r["PNL-Kitchen"] + r["PNL-Lauandry"] - r.TeslaWC }))**
3

@Patrick808 It’s because you have null values in your math operations. If any operands in a mathematic operation are null, the operation returns null.

There are two things I can think of that will cause this:

  1. You have points with null values (pre-pivot)
  2. Your timestamps don’t perfectly align, so as a result of the pivot, some rows don’t have values for some of the columns used in your map operation.

I suspect it’s probably #2. There are a few solutions:

  1. Use fill() to fill each of the columns in the pivoted data. Pivoted data does make this process a little cumbersome since fill() can only fill one column at a time:

    from(bucket: "IotaHourly")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["ct"] == "AC1" or r["ct"] == "AC2" or r["ct"] == "AC3" or r["ct"] == "PNL-Kitchen" or r["ct"] == "PNL-Laundry" or r["ct"] == "TeslaWC")
    |> drop(columns: ["device", "_measurement", "_start", "_stop", "_field"])
    |> pivot(rowKey: ["_time"], columnKey: ["ct"], valueColumn: "_value")
    |> fill(column: "_AC1", value: 0)
    |> fill(column: "AC2", value: 0)
    |> fill(column: "AC3", value: 0)
    |> fill(column: "PNL-Kitchen", value: 0)
    |> fill(column: "PNL-Lauandry", value: 0)
    |> fill(column: "TeslaWC", value: 0)
    |> map(fn: (r) => ({r with House: r._AC1 + r.AC2 + r.AC3 + r["PNL-Kitchen"] + r["PNL-Lauandry"] - r.TeslaWC }))

  2. You can normalize the timestamps to a precision where points should all align. For example, if you’re seeing sub-second differences in timestamps between all the different fields, you can normalize the timestamps to the second. To do this, use truncateTimeColumn() before you pivot the data:

    from(bucket: "IotaHourly")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["ct"] == "AC1" or r["ct"] == "AC2" or r["ct"] == "AC3" or r["ct"] == "PNL-Kitchen" or r["ct"] == "PNL-Laundry" or r["ct"] == "TeslaWC")
    |> drop(columns: ["device", "_measurement", "_start", "_stop", "_field"])
    |> truncateTimeColumn(unit: 1s)
    |> pivot(rowKey: ["_time"], columnKey: ["ct"], valueColumn: "_value")
    |> map(fn: (r) => ({r with House: r._AC1 + r.AC2 + r.AC3 + r["PNL-Kitchen"] + r["PNL-Lauandry"] - r.TeslaWC }))

Truth be told, you may need to do both of these. If timestamps still don’t align after normalizing/truncating the time values, you will still have to fill all the null columns.

4

I don’t think either explanation checks out.

This is what the table from the pivot shows:

FluxPivotMap
FluxPivotMap1920×1088 163 KB

5

Ah, I see the issue. You have an underscore (_) in front of AC1 in your map() call. That column doesn’t exist in the data, so it’s returning null. Remove the underscore and you should be good to go.

6

Doh… Thanks. So much for checking seven times!

7

No problem. Happy to help!