Calculated field and additional column with currency

Hello,
is there an easier and more performant solution to add the currency column here?

t1=
  from(bucket: "VH_Messwerte")
  |> range(start: ${ErsterTagJahr}, stop: now())
  |> filter(fn: (r) => r["topic"] == "Heizen/WP/GesElEnergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()
  |> map(fn: (r) => ({r with _value: (float(v: r._value) / 1000.0 )} ))
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)

t2=
 from(bucket: "VH_Messwerte")
  |> range(start: ${ErsterTagJahr}, stop: now())
  |> filter(fn: (r) => r["topic"] == "Heizen/WP/GesElEnergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()
  |> map(fn: (r) => ({r with _value: ((float(v: r._value) / 1000.0) *${EUR_kWh_WP} )} ))
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)


  join(tables: {t1: t1, t2: t2}, on: ["_time"])

Thanks You

Hi @matze1708,
If you are using InfluxDB Cloud / InfluxDB OSS 2.5 you can optimize slightly like this:


rawdata = () => from(bucket: "VH_Messwerte")
  |> range(start: ${ErsterTagJahr}, stop: now())
  |> filter(fn: (r) => r["topic"] == "Heizen/WP/GesElEnergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()
 

t1=
rawdata()
|> map(fn: (r) => ({r with _value: (float(v: r._value) / 1000.0 )} ))
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)
  

t2=
rawdata()
  |> map(fn: (r) => ({r with _value: ((float(v: r._value) / 1000.0) * ${EUR_kWh_WP} )} ))
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)


  join(tables: {t1: t1, t2: t2}, on: ["_time"])

This saves retrieval twice. If you can also find a way of swapping your map and aggregate windows round i.e.

  |> map(fn: (r) => ({r with _value: (float(v: r._value) / 1000.0 )} ))
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)

//to

  
  |> aggregateWindow(every: 1y, fn: sum, createEmpty: false)
|> map(fn: (r) => ({r with _value: (float(v: r._value) / 1000.0 )} ))

This would also help as aggregate window is a pushdown function

Thank you! This is exactly what I’m looking for!

Is there also a way to rename the fields at the end?
for example Value and Currency

1 Like

Hi @matze1708,
Not a problem! You could use set() function | Flux 0.x Documentation