Combine/Join/Pivot multiple time series data for .csv export

Fluxlang
1

Dear Influx Community

I have collected time series data from multiple devices > 10 (e.g. multimeters) over multiple month.

→ 

|     device    |    _value    |     _time                         |  _field        | _measurement
|     multi_1   |     10       |     2022-11-11T10:01:30.814Z      |  current_(mA)  | long_test_1
....


|     device    |    _value    |     _time                         |  _field         | _measurement
|     multi_2   |     0.1      |     2022-11-11T10:01:32.123Z      |  voltage_(V)    | long_test_1
....

|     device    |    _value    |     _time                         |  _field         | _measurement
|     multi_3   |     1.2      |     2022-11-11T10:01:32.412Z      |  voltage_(V)    | long_test_1
....

Now I want to join all these data rows into one .csv file, so I can analyse it via matlab/python. Do calculations and maybe test some machine learning.

To do that, I am using the Notebooks feature of the web interface and created a flux query like that:

multi_1_current = from(bucket: "data") |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["device"] == "multi_1")
  |> filter(fn: (r) => r["_field"] == "current_(mA)")
  |> drop(columns: ["_field", "device", "_measurement"])
  |> rename(columns: {_value: "multi_1_current_(mA)"})

multi_2_voltage = from(bucket: "data") |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["device"] == "multi_2")
  |> filter(fn: (r) => r["_field"] == "voltage_(V)")
  |> drop(columns: , "_field", "device", "_measurement"])
  |> rename(columns: {_value: "multi_2_votlage_(V)"})

multi_3_voltage = from(bucket: "data") |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["device"] == "multi_3")
  |> filter(fn: (r) => r["_field"] == "voltage_(V)")
  |> drop(columns: ["_field", "device", "_measurement"])
  |> rename(columns: {_value: "multi_3_voltage_(V)"})


result_1 = join(tables: {multi_1_current : multi_1_current , multi_2_voltage : multi_2_voltage }, on: ["_time"], method: "inner")

join(tables: {result_1 : result_1  multi_3_voltage : multi_3_voltage }, on: ["_time"], method: "inner")

Now I have multiple problems:

  1. I cant joint more then 2 tables
  2. Connecting multiple joins wont work
  3. Joint generate a lot of additional rows → _start_multi_1_current ; _stop_multi_1_current ; _start_multi_2_voltage ; _stop_multi_2_voltage …
  4. This approach feels a bit laborious, is there a better way?

My goal is to have a table

|   _time                       |  multi_1_current_(mA)  |  multi_2_voltage_(V)  |   multi_3_voltage_(V)
|    2022-11-11T10:01:30.814Z   |     10                 |    0.1                |   1.2
....