Operation with values of different measurements

Fluxlang
1

Hello Community,

just switched to InfluxDB 2.0 and fairly new to Flux and still trying to figure out some things…maybe you can point me to a solution for this requirement:
I’m storing currency conversion rates in one measurement and currency-based values in another measurement, lets say something like this:
time, value, from, to
10:00, 1.03, GBP, EUR
10:15, 1.02, GBP, EUR

time, amount, currency
10:00, 23.55, GBP
10:01, 54.01, GBP

Now I’m looking for a transformation/query that would allow me to convert the “amount” in GBP to EUR based on the conversion rate given at a specific time.
I know I can do a map function to apply a operation to every _value in a table, but in my case I’d need a “dynamic” operation, e.g. multiply _value with conversionrate at the time of the _value’s timestamp.

|> map(fn: (r) => ({
      r with
      _value: _value * $conversionrate (from the other table at this row's _time)
    })
  )

Is there some way to use a function within the map function to get the conversion rate from the other measurement?

Thanks!

2

@Reinhard_Weber You can query the exchange rate as part of the map() call. In the example below, I create a custom function that retrieves the conversion rate for a particular currency based on time and returns it as a scalar value. It then uses that custom function inside of the map call:

import "experimental"

getConversionRate = (time, fromCurrency, toCurrency) => {
  conversionRate =
    from(bucket: "example-bucket")
      |> range(start: experimental.subDuration(d: 1h, from: time), stop: time)
      |> filter(fn: (r) => r._measurement == "exchangeRates" and r.from == fromCurrency and r.to == toCurrency)
      |> last()
      |> findColumn(fn: (key) => true, column: "value")
  return conversionRate[0]
}

convertTo = "EUR"

data
  |> map(fn: (r) => ({
    r with
      _value: _value * getConversionRate(time: r._time, fromCurrency: r.currency, toCurrency: convertTo)
    })
  )

…or something similar.

3

Thanks @scott!
That looks very promising and I was guessing that this would work somehow like this, just couldn’t figure it out! The “experimental.subDuration” looks interesting.
Exactly what I was looking for!

4

Thank you @scott you saved me lots of effort figuring this out by myself!
Only thing it noticed is that the subDuration should not contain a negative “d:” this would lead to adding the one hour, should be “1h” to substract from “time”

5

Nice catch! Updated the example.