Simple math question

Fluxlang
,
1

Hi there,

being a beginner with InfluxDB I just want to multiply one value (“etoday”) and output it to the graph.

However, I don’t know how to achieve this.

from(bucket: "myBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "etoday" or r["_field"] == "htotal" or r["_field"] == "powact" or r["_field"] == "string1" or r["_field"] == "string2" or r["_field"] == "wbcharge")
  |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ et_zoom: r["etoday"] * 100 }))
  |> yield()

gives “No results”

Using

|> map(fn: (r) => ({ r with _value: r.etoday * 100 }))

for the map function gives 0.

Thanks for a little push into the right direction.

2

@Tino The from() function returns a different data structure than SQL-like languages. Fields are returned in two separate columns:

  • _field: Stores the field key
  • _value: Stores the field value

Also, by default, data is grouped by field, so you’ll have a separate table per field. So the structure of your data when queried looks something like this:

_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement etoday 1
2024-05-29T00:01:00Z myMeasurement etoday 1
_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement htotal 1
2024-05-29T00:01:00Z myMeasurement htotal 1
_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement powact 1
2024-05-29T00:01:00Z myMeasurement powact 1
_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement string1 1
2024-05-29T00:01:00Z myMeasurement string1 1
_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement string2 1
2024-05-29T00:01:00Z myMeasurement string2 1
_time _measurement _field _value
2024-05-29T00:00:00Z myMeasurement wbcharge 1
2024-05-29T00:01:00Z myMeasurement wbcharge 1

To perform the operation you’re trying to perform, you need to pivot your fields into columns using the pivot function:

from(bucket: "myBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "etoday" or r["_field"] == "htotal" or r["_field"] == "powact" or r["_field"] == "string1" or r["_field"] == "string2" or r["_field"] == "wbcharge")
  |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

This will change the structure of your data to look something like this:

_time _measurement etoday htotal powact string1 string2 wbcharge
2024-05-29T00:00:00Z myMeasurement 1 1 1 1 1 1
2024-05-29T00:01:00Z myMeasurement 1 1 1 1 1 1

With this structure, you can perform the map operating you’re trying to do:

from(bucket: "myBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "etoday" or r["_field"] == "htotal" or r["_field"] == "powact" or r["_field"] == "string1" or r["_field"] == "string2" or r["_field"] == "wbcharge")
  |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r.etoday * 100 }))
3

Hi Scott,

thank you much, this (of course) works like a charm!
But now my graph only shows the value of the new value of etoday…

How do I get the other, original values into the graph?

If you can give me a hint to the right location in the docs I’d be more than happy to try to find a solution by myself. There are examples for doing math operations on data, and there are examples for displaying data – but I can’t find the connection.
Obviously it’s not easy to get a start with this.

Thanks in advance

Tino

4

@Tino What are you using to visualize the data? InfluxDB dashboard? Grafana?

5

Hi Scott, for now I’m “playing” around with the InfluxDB dashboard which suffices.

6

@Tino The reason I ask is because InfluxDB dashboards and Grafana give you different control over how data is visualized based on the structure of the query results. InfluxDB dashboards expect “unpivoted” data where the field key is stored in the _field column and the field value is stored in the _value column. In the original solution above, we pivoted the data to perform math on the etoday field. For this to be graphed how you want in an InfluxDB dashboard, you have to make sure the query results are unpivoted. There are a few ways to do this:

Use experimental.unpivot() to restructure your pivoted data

import "experimental"

from(bucket: "myBucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_field"] == "etoday" or r["_field"] == "htotal" or r["_field"] == "powact" or r["_field"] == "string1" or r["_field"] == "string2" or r["_field"] == "wbcharge")
    |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
    |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({r with etoday: r.etoday * 100}))
    |> experimental.unpivot()

Don’t pivot the data and union two streams together

import "experimental"

etoday = from(bucket: "myBucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
    |> filter(fn: (r) => r["_field"] == "etoday")
    |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
    |> map(fn: (r) => ({r with _value: r._value * 100}))

everythingElse = from(bucket: "myBucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
    |> filter(fn: (r) => r["_field"] == "etoday" or r["_field"] == "htotal" or r["_field"] == "powact" or r["_field"] == "string1" or r["_field"] == "string2" or r["_field"] == "wbcharge")
    |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

union(tables: [etoday, everythingElse])