JOIN from different measurements in one row

Fluxlang
1

I need to combine two tables by name and time and get the result in one row in the form
_value_sdc, _value_sdbi, _value_sdco,_value_sdc. These data arrive at the same time but have different measurement and a common tag name.
. The request returns the correct response, but in several lines. Tell me how to do it.

water = from(bucket: "vending")
  |> range(start: 	-30m )
  |> filter(fn: (r) => r["_measurement"] == "volume")
   |> filter(fn: (r) => r["_field"] == "sdw")
  |>last()
  |> drop(columns: ["uidi","_start","_stop"])

 card =  from(bucket: "vending")
  |> range(start: 	-30m )
  |> filter(fn: (r) => r["_measurement"] == "money")
   |> filter(fn: (r) => r["_field"] == "sdc" or r["_field"] == "sdco" or r["_field"] == "sdbi")
  |>last()
  |> drop(columns: ["uidi","_start","_stop"])



join (
tables: {sdw:water, sdc:card}, 
 on: ["_time","name"], 
method: "inner"
)
|> keep(columns: ["_value_sdc", "_value_sdw","_value_sdbi","_value_sdco", "_time","name"])

result:

image
image1628×253 20.3 KB

2

Could you send your input data? it is quite hard to see where the columns should combine in the join.
Furthermore try to use join inner since join is deprecated: join.inner() function | Flux Documentation

Then within a join.inner you could use as: for example: as:(l, r) => ({l with label: r._value})

3

Thank you for reply. The issue have been resolved using PIVOT and join functions.

water = from(bucket: "vending")
  |> range(start: 	-30m )
  |> filter(fn: (r) => r["_measurement"] == "volume")
  |> filter(fn: (r) => r["_field"] == "sdw")
  |>last()
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> drop(columns: ["uidi","_start","_stop"])

 card =  from(bucket: "vending")
  |> range(start: 	-30m )
  |> filter(fn: (r) => r["_measurement"] == "money")
  |> filter(fn: (r) => r["_field"] == "sdc" or r["_field"] == "sdco" or r["_field"] == "sdbi" or r["_field"] == "sdm")
  |>last()
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> drop(columns: ["uidi","_start","_stop"])



join (
tables: {sdw:water, sdc:card}, 
 on: ["_time","name"], 
method: "inner"
)