Combining data from two similar tables

Fluxlang
,
1

Feels like a straightfoward case, but I haven’t been able to manage the query.

I have two _fields that contain values for two pumps that we’re monitoring. They both belong to the same station. I’m trying to make a query that provides a combined output table with three columns (for output to Grafana), like so:

Time						Pump	Value
2021-03-22T14:14:06.340Z	Pump1	71.15999603271484	
2021-03-22T14:14:06.340Z	Pump2	164.20999145507812
2021-03-22T14:14:06.340Z	Pump1	73.70999145507812

or alternatively:

Time						Pump1	                        Pump2
2021-03-22T14:14:06.340Z	71.15999603271484
2021-03-22T14:14:06.340Z		                            164.20999145507812
2021-03-22T14:14:06.340Z	73.70999145507812

With separate queries I get the results for each pump just fine, but I can’t seem to combine them into a single table. When I do a union like below, I get both separate tables in one view (and can switch between them in Grafana), but I don’t get a single table.

Pump1 = from(bucket: "StationData")
  |> range(start: -3h)
  |> filter(fn: (r) =>
    r._measurement == "plc" and
    r._field == "Pump1" and
    r.host == "Station1"
  )

Pump2 = from(bucket: "StationData")
  |> range(start: -3h)
  |> filter(fn: (r) =>
    r._measurement == "plc" and
    r._field == "Pump2" and
    r.host == "Station1"
  )

union(tables: [Pump1, Pump2])

Using ‘join’ I can get the results together in a single table, but, as one would expect in a join, I get a lot of duplicate fields

It feels like I want to do something like convert the field-names for Pump1 and Pump2 to both be ‘Pump’ and then do the union. So I’ve been looking at the ‘Rename’ function, but without much success so far.

Anyone can give me a push in the right direction?

2

@christiaan When you union two streams, it maintains the group keys, so the data is still partitioned into separate tables by series (common measurement, tag set, and field key).
To group everything into a single table, you can just use group() (with no parameters).

In your particular use case, you don’t even need to create two streams and union them together. You can just do:

from(bucket: "StationData")
  |> range(start: -3h)
  |> filter(fn: (r) =>
    r._measurement == "plc" and
    (r._field == "Pump1" or r._field == "Pump2") and
    r.host == "Station1"
  )
  |> group()
2 Likes
3

@scott That did the trick! Figured it’d be something simple, but not quite this simple. Thanks a bunch!

4

@christiaan No problem! Happy to help!

5

@scott
I ran into same problem,
The group is not combining two tables instead it remove all rows of second table.

Could you please help?

from(bucket: "STAGING")
	|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "zone")
  |> filter(fn: (r) => r["inst"] == "LEFRAK_SHORELINE")
  |> filter(fn: (r) => r["group"] == "RESIDENT_LOUNGE")
  |> filter(fn: (r) => r["zone"] == "LEFRAK_SHORELINE_WEST_LOUNGE_FULL_ROOM" or r["zone"] == "LEFRAK_SHORELINE_WEST_LOUNGE_GAME_ROOM_FULL_ROOM")
  |> filter(fn: (r) => r["cam"] == "LEFRAK_SHORELINE_WEST_LOUNGE" or r["cam"] == "LEFRAK_SHORELINE_WEST_LOUNGE_GAME_ROOM")
  |> filter(fn: (r) => r["_field"] == "count")
  |> group()```
6

so its working for small time window (max 15 mins), not combining if i change it to 1h.

7

@krutarth.joshi009 What version of InfluxDB/Flux are you using? Can you provide any sample output?