Substract first data point to get relative values

[Glynos]

Hello, I am trying to run a flux script which will subtract first data point from each uniq time series. This code is working but its really slow because of calling getFirstValue in map function over many records.
How to make it more effective?

I was trying to create array with field names and its first values instead of getFirstValue function but I was not able to make it work.

This is my actual code:

values = from(bucket: “myBucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “measurement” and r[“type”] != “temp” and r[“type”] != “lambda”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> drop(columns: [“_start”, “_stop”])

firstValues = values
|>first()
//|> yield(name: “First value”)

firstRec = firstValues |> findRecord(fn: (key) => true , idx: 0)

fieldName = firstRec._field

FNC_firstValue = (table=<-, fieldName=“”) =>
table
|> filter(fn: (r) => r[“_field”] == fieldName)
|> first()
|> findRecord(fn: (key) => true , idx: 0)

actualFirstRec = FNC_firstValue(table: firstValues, fieldName: fieldName)

getFirstValue = (fieldName=“”) => {
actualFirstRec = if actualFirstRec._field == fieldName then actualFirstRec else FNC_firstValue(table: firstValues, fieldName: fieldName)
return actualFirstRec
}

FNC_getRelativeValues = (dataTable=<-) => { //, fieldName=“”
return dataTable
|> map(fn: (r) => ({r with _value: r._value - getFirstValue(fieldName: r._field)._value}))
|> yield(name: “relative”)
}

FNC_getRelativeValues(dataTable: values)

[Anaisdg]

Hello @Glynos,
Welcome!
hello I’m a little confused why you need actualFirst Rec.
It feels like you could just use the fieldName?
Since you make
actualFirstRec = FNC_firstValue(table: firstValues, fieldName: fieldName)
Then this
actualFirstRec = if actualFirstRec._field == fieldName then actualFirstRec
will always be true?
Or am I missing something obvious? It feels like I might.

The the only thing I can really think of.

[Glynos]

Hello @Anaisdg,

firstRec = firstValues |> findRecord(fn: (key) => true , idx: 0)
fieldName = firstRec._field
actualFirstRec = FNC_firstValue(table: firstValues, fieldName: fieldName)

Is only for saving first record (r) with designated fieldName (r._field) to global variable - initializing it.

…
Maybe I will start again and describe my task from beginning, because I think there could be much easier solution. I have measurement “measurement” in which are some Influx Tables (by Influx Table I mean time series with uniq combinations of _measurement, _field and tags).
And I need to substract first value from each data series (in selected time interval) from every value in the series to get relative values. Something like this.

Top picture: are original data saved in influx:
Bottom picture: are relative values (first data point is substraced from each timeseries).

My original code posted have this problem right here:

• In map I am iterating over every record I need to recalculate (It contains like 20 data series - with different r._field = fieldName)

• In every iteration I am calling function getFirstValue witch check if in actualRecord is the first record of the time series with the same fieldName (r._field). If not, I am calling function FNC_firstValue wich will search in the data set the first record and save it to actualRecord.

• The problem is that calling this function (getFirstValue) in every iteration (map) is really slowing the query (For 20 time series is execution duration over 150 seconds).
  FNC_getRelativeValues = (dataTable=<-) => {
  return dataTable
  |> map(fn: (r) => ({r with _value: r._value - getFirstValue(fieldName: r._field)._value}))
  |> yield(name: “relative”)
  }

Solution for one time series:

1. Find only data from one time series (fieldName == r._field).
2. Save first value (_firstValue)
3. Iterate over data with map (but subtracting constant, not calling another function)
4. This is much more faster for execution because there is no call function in map
5. But I don’t know how I can run this function with all fieldNames in wanted data set.

FNC_getRelativeValues = (dataTable=<-, , fieldName=“”) => {
_firstValue = FNC_firstValue(table: dataTable |> first(), fieldName: fieldName)._value
return dataTable
|> filter(fn: (r) => r[“_field”] == fieldName)
|> map(fn: (r) => ({r with _value: r._value - _firstValue))
|> yield(name: “relative”)
}

Note there could be much easier and better solution with some function I don’t know about.