Generate an additional column from other columns

1

Hi everyone,

i have a grafana cloud instance, which querys data from an influxdb cloud instance (2.0 / TSM). This output are 4 columns (1. SensorID, 2. CountofValue1, 3. CountofValue2, 4. CountofValue3)

This table is being generated by 3 different which look like the following:

from(bucket: “TestData”)
|> range(start: -24h)
|> filter(fn: (r) => r[“_measurement”] == “testmeasurement”)
|> filter(fn: (r) => r.Project == “${Projects}”)
|> filter(fn: (r) => r._field == “Value1” and r._value >= -1)
|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({r with Value1Count: 1}))
|> count(column: “Value1Count”)
|> group()

the query varies only regarding the field filter i apply as well as the value changing from 1 / 2 and 3.

I now want to implement the option that if there is no value inside of the column it only shows a 0 as the count value. Besides that i also want to implement a 5th column which generates a number of 0 or 1 (1 if all three values have at least one count, and 0 if one of those three has a count of zero)

How can i accomplish that task ?

2

For your first question, maybe a special map() function like this?

from(bucket: "TestData")
  |> range(start: -24h)
  |> filter(fn: (r) => r["_measurement"] == "testmeasurement")
  |> filter(fn: (r) => r.Project == "${Projects}")
  |> filter(fn: (r) => r["_field"] == "Value1" and r["_value"] >= -1)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with Value1Count: 1}))
  |> count(column: "Value1Count")
  |> group()
  |> map(fn: (r) => ({
      r with
      _value: if r._value == null then 0 else r._value
  }))

Assuming the above works for all 3 queries (value1, value2, and value3), then how do you combine everything into one output stream with 4 columns (and soon hopefully, 5 columns)?