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: