Operation with values of different measurements

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?


@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"

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

…or something similar.

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!

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”

Nice catch! Updated the example.