Flux query for multiple tables each with the last value subtracted from all values

Fluxlang
,
1

Hi, Experts! I am storing a number of voltage measurements in an influxdb2.
Previously I used separate queries to zero out each device and display every one side by side:

from(bucket: "hub")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Device1")
  |> filter(fn: (r) => r["_field"] == "Voltmeter1")
  |> map(fn: (r) => ({r with _value: (r._value - 9.996286)}))

But as more devices enter the database this gets tedious. I am looking for a method query all devices and automatically subtract the most recent value from all values. I tried the following with no success:

from(bucket: "hub")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "Voltmeter1")
  |> map(fn: (r) => ({r with _value: r._value - findColumn(tables: last(), fn: (key) => true, column: "_value")[0]}))

invalid: error @4:68-4:74: missing required argument tables

2

Hi @reps,
you are not far off:

import "sampledata"

lastVal= sampledata.int()
    |> last()   
    |> tableFind(fn: (key) => true) 
    |> getRecord(idx: 0)

sampledata.int()
  |> map(fn: (r) => ({ r with _value: r._value - lastVal._value }))
3

Thank you so much for your help, Jay! I have adapted your example to my scenario and it does indeed subtract the last value of one measurement from all values. However I was hoping zero out each measurement separately, so that the very small differences between the values becomes visible. Do you think that’s possible?

data = from(bucket: "hub")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "Voltmeter1")

lastVal = data
  |> tableFind(fn: (key) => true) 
  |> getRecord(idx: 0)

zeroed_output = data
  |> map(fn: (r) => ({r with _value: r._value - lastVal._value}))
  |> yield(name: "zeroed")

Before these were all sitting around 10

Clipboard01
Clipboard011726×889 156 KB

4

hmm fo that you would need to build a custom function with a little more logic:

// Start by selecting data from the schema browser or typing flux here
data = from(bucket: "hub")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "Voltmeter1")



getLast = (m) => {
   lv = data
  |> filter(fn: (r) => r["_measurement"] == m)
  |>last()
  |> tableFind(fn: (key) => true) 
  |> getRecord(idx: 0)
    
    return lv
}

zeroed_output = data
  |> map(fn: (r) => ({r with _value: r._value - getLast(m: r._measurement)._value}))
  |> yield(name: "zeroed")

I have no idea how well this query will perform over a large dataset

5

Thank you so much! This is exactly the minimalist solution I was looking for. You are right, it’s super slow, so for practical purposes I’ll probably stick with something else. But very educating solution for sure.

6

Hi @Jay_Clifford,

this is working, but the query is really slow.
Calling getLast(m: r._measurement)._value in map really slow down everything.

When I use constant values then the performance is good.

I was thinking about making array/dict with last values and names of measurements.
Something like this:

last_values = {"measurement1": 12.0, "measurement2": 15.0}

zeroed_output = data
  |> map(fn: (r) => ({r with _value: r._value - last_values[r._measurement] }))
  |> yield(name: "zeroed")

This works much better. But I don’t know how can I make last_values dynamically in query. Is there any way?