Join which allows empty datasets or default values

I have two data sets I would like to combine into a single table. The table should include mean rsrp for both 4G and 5G. However not all devices are in 5G coverage and will not report any rsrp.

Set A
Serial  rsrp_4g
000001  -54
000002  -74
000003  -64
000004  -54
000005  -54
Set B
Serial  rsrp_5g
000001  -85
000002  -75
000003  -65

Using join I end up with just the rows which has both 4g and 5g. The rows with 4G only are left out

join(A,B)
Serial  rsrp_4g rsrp_5g
000001  -54     -85
000002  -74     -75
000003  -64     -65

Using union I end up with duplicate serial numbers

union(A,B)
Serial  rsrp_4g rsrp_5g
000001  -54    
000002  -74    
000003  -64    
000004  -54
000005  -54
000001           -85
000002           -75
000003           -65

What I would like is to combine both data sets like join, but with NULL or some default value where there are none. Any ideas on how to get to that result?

dontexist.combine(A,B)
Serial  rsrp_4g rsrp_5g
000001  -54     -85
000002  -74     -75
000003  -64     -65
000004  -54     NULL
000005  -54     NULL

Hi @eriktar,
I hope you are doing well! Sadly we do not currently support outer joins within Flux. It is a feature our development team are currently investigating. I would ask that you voice your need here and the scenario. I will also push this internally: add outer join · Issue #2756 · influxdata/flux · GitHub

I believe a user may have found a workaround in meantime although a little “hacky”. Once my colleague comes online let me confirm with her and I will get back to you.

Thanks @Jay_Clifford. I added an (admittedly not all that usefull) comment on github.

Looking forward to a hacky solution. Cannot be worse than my current implementation where I do a query for each parameter pr serial, then joining the results in perl. (rsrp, and band pr 4g and 5g so four queries pr serial which there are 12.000 off at the moment.)

@eriktar ,
This is the post submitted by another dev attempting to create a left join. Let me know if you have any questions: Compare lists and find members of A not in B :slight_smile:

Hello @eriktar,
You could also try a union and then a fieldsAsCol()

That should get you what you want.

1 Like

@Anaisdg, if I understand the proposal @Jay_Clifford was linking to - it’s basically experimantal.join?

I’m posting the real code and data her, minus the actual serial numbers. Every SERIAL is uniq pr 5G and 4G. The 4G and 5G data get the timestamp set by the same process so they are guaranteed to match across 4G and 5G. Timestap differences shown here is due to the time it takes me to edit - run - copy the files and outputs.

I have tried multiple variants of the band_xG. Using map, group, neither. But the result always end up the same. I have not yet tried the fieldsAsCol() suggestion.

import "experimental"

band_4G =from(bucket:"fmbb/autogen")
 |> range(start: -1h, stop: -0h)
 |> filter(fn: (r) => r._measurement == "cellular"  and
  r._field == "band"  and
  r.serial_number =~ /SERIALS/ and
  r.carrier == "4g" 
 )
 |> last()
 |> group(columns: ["serial_number"])
// |> map(fn:(r) => ({serial_number:r.serial_number,_value: r._value}))



band_5G =from(bucket:"fmbb/autogen")
 |> range(start: -1h, stop: -0h)
 |> filter(fn: (r) => r._measurement == "cellular"  and
  r._field == "band"  and
  r.serial_number =~ /SERIALS/ and
  r.carrier == "5g" 
 )
 |> last()
 |> group(columns: ["serial_number"])
// |> map(fn:(r) => ({serial_number:r.serial_number,_value: r._value}))


band_join_experimental = experimental.join(
  left: band_5G,
  right: band_4G,
  fn: (left, right) => ({
    left with
    lv: left._value,
    rv: right._value,
  })
)

band_join = join(
    tables:{b4g: band_4G,b5g: band_5G},
    on: ["_time"]
)


// band_join_experimental
band_join
// band_4G
//band_5G

This here is the result from band_4G

--- 1
_start                          _stop                           _time                           _value  _field  _measurement  carrier  event_type  host        serial_number
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:59.680798307Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:07.951028618Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:12.65139133Z   3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:52.2506839Z    7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:09.450994627Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:18.080115384Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:16.380093804Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:31.051109215Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:49.580320951Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:33.150752235Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:11.88083486Z   3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:26.980719112Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:43.951497901Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:33.950908606Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:31.551357797Z  28      band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:35.780340747Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:13.280945134Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:40:48.75125927Z   7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:39.251149174Z  7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:53.880066019Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:15.180555959Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:42:00.280151373Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:38.68014069Z   3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:28.751131459Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:57.480119039Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:08.850851654Z  7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:50.650852376Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:36.650830672Z  7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:54.080062187Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:13.580028059Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:21.180114438Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:58.450624515Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:17.080321766Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:33.880406058Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:44.750583122Z  7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:50.380113762Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:03.080244902Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:41.25059569Z   3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:39.151341346Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:15.380308241Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:36.980747631Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:04.180180129Z  3       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:52.980224702Z  7       band    cellular      4g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:41.25059569Z   7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:39.350996327Z  7       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:38.350569001Z  3       band    cellular      4g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:00.393115167Z  2021-11-17T06:42:00.393115167Z  2021-11-17T06:41:47.08065926Z   3       band    cellular      4g       cellular    cmp-swarm8  SERIAL

Output from band_5G

--- 1
_start                          _stop                           _time                           _value  _field  _measurement  carrier  event_type  host        serial_number
2021-11-17T05:42:37.675327224Z  2021-11-17T06:42:37.675327224Z  2021-11-17T06:41:52.2506839Z    78      band    cellular      5g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:37.675327224Z  2021-11-17T06:42:37.675327224Z  2021-11-17T06:42:19.880614892Z  78      band    cellular      5g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:37.675327224Z  2021-11-17T06:42:37.675327224Z  2021-11-17T06:42:14.850732429Z  78      band    cellular      5g       cellular    cmp-swarm7  SERIAL
2021-11-17T05:42:37.675327224Z  2021-11-17T06:42:37.675327224Z  2021-11-17T06:42:23.580216037Z  78      band    cellular      5g       cellular    cmp-swarm8  SERIAL
2021-11-17T05:42:37.675327224Z  2021-11-17T06:42:37.675327224Z  2021-11-17T06:41:39.350996327Z  78      band    cellular      5g       cellular    cmp-swarm7  SERIAL

Output from band_join

--- 1
_field_b4g  _field_b5g  _measurement_b4g  _measurement_b5g  _start_b4g                      _start_b5g                      _stop_b4g                       _stop_b5g                       _time                           _value_b4g  _value_b5g  carrier_b4g  carrier_b5g  event_type_b4g  event_type_b5g  host_b4g    host_b5g    serial_number_b4g  serial_number_b5g
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:42:54.351129493Z  7           78          4g           5g           cellular        cellular        cmp-swarm7  cmp-swarm7  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:25.880028651Z  3           78          4g           5g           cellular        cellular        cmp-swarm8  cmp-swarm8  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:25.880028651Z  3           78          4g           5g           cellular        cellular        cmp-swarm8  cmp-swarm8  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:42:14.850732429Z  7           78          4g           5g           cellular        cellular        cmp-swarm7  cmp-swarm7  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:25.880028651Z  7           78          4g           5g           cellular        cellular        cmp-swarm8  cmp-swarm8  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:25.880028651Z  7           78          4g           5g           cellular        cellular        cmp-swarm8  cmp-swarm8  SERIAL      SERIAL
band        band        cellular          cellular          2021-11-17T05:43:26.208481765Z  2021-11-17T05:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:43:26.208481765Z  2021-11-17T06:42:41.250671663Z  7           78          4g           5g           cellular        cellular        cmp-swarm7  cmp-swarm7  SERIAL      SERIAL

Output from band_join_experimental

--- 1
_field  _measurement  _start                         _stop                          _time                           _value  carrier  event_type  host        lv  rv  serial_number
band    cellular      2021-11-17T05:44:01.80917651Z  2021-11-17T06:44:01.80917651Z  2021-11-17T06:43:56.350752262Z  78      5g       cellular    cmp-swarm7  78  7   SERIAL
band    cellular      2021-11-17T05:44:01.80917651Z  2021-11-17T06:44:01.80917651Z  2021-11-17T06:43:25.880028651Z  78      5g       cellular    cmp-swarm8  78  3   SERIAL
band    cellular      2021-11-17T05:44:01.80917651Z  2021-11-17T06:44:01.80917651Z  2021-11-17T06:43:41.050646745Z  78      5g       cellular    cmp-swarm7  78  7   SERIAL
band    cellular      2021-11-17T05:44:01.80917651Z  2021-11-17T06:44:01.80917651Z  2021-11-17T06:43:25.880028651Z  78      5g       cellular    cmp-swarm8  78  7   SERIAL
band    cellular      2021-11-17T05:44:01.80917651Z  2021-11-17T06:44:01.80917651Z  2021-11-17T06:43:43.350979446Z  78      5g       cellular    cmp-swarm7  78  7   SERIAL

Hello @eriktar,
The solution that @Jay_Clifford posted is hard to understand. can you give union() and fieldsascol a try please?

Closing in on a solution, but not quite there. As you see in the output, the 5g data are not on the same line as the 4g data, but separate table.

import "influxdata/influxdb/v1"

band_4G =from(bucket:"fmbb/autogen")
 |> range(start: -1h, stop: -0h)
 |> filter(fn: (r) => r._measurement == "cellular"  and
  (r._field == "cell_id" or r._field == "band" or r._field == "rsrp")  and
  r.serial_number =~ /SERIALS and
  ( r.carrier == "4g" ) 
 )
 |> last()


band_5G =from(bucket:"fmbb/autogen")
 |> range(start: -1h, stop: -0h)
 |> filter(fn: (r) => r._measurement == "cellular"  and
  ( r._field == "band" or r._field == "rsrp")  and
  r.serial_number =~ /SERIALS/ and
  r.carrier == "5g"
 )
 |> last()


band_union = union(
    tables: [band_4G,band_5G]
    )
 |> v1.fieldsAsCols()


band_union
--- 1
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  band  cell_id   rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:11.251029726Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51766528  -75
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:45.050608818Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19262211  -76
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:57.350567856Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19164931  -85
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:04.251300245Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     18547970  -86
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:34.250772382Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     20471811  -76
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:48.950547574Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19843075  -79
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:44.451112877Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51782144  -111
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:49.051098301Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19024130  -74
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:36.051580173Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     19461380  -80
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:10.350945381Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19969026  -75
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:26.350577705Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51959303  -84
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:22.650900254Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     21263364  -70
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:14.450757421Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51579649  -100
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:22.550934908Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     18320896  -76
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:07.851350852Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19882500  -87
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:07.750604069Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     18093571  -86
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:19.150766623Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51485960  -87
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:04.150913156Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     19442948  -92
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:54.051525522Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     51994371  -68
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:45.250762332Z  cellular      4g       cellular    cmp-swarm7  SERIAL  8     20936202  -97
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:25.750634717Z  cellular      4g       cellular    cmp-swarm7  SERIAL  3     18676483  -83
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:22.950569443Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     17511170  -94
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:06.651060707Z  cellular      4g       cellular    cmp-swarm7  SERIAL  7     20299015  -103
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:39.181016488Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     20303617  -77
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:01.080186268Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17609984  -96
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:21.180215139Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17596930  -82
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:08.880341488Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17462533  -72
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:11.88061486Z   cellular      4g       cellular    cmp-swarm8  SERIAL  3     17644039  -82
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:27.880280482Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17649665  -54
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:40.280947139Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17861382  -81
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:58.780628909Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     18535170  -78
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T07:55:06.980272172Z  cellular      4g       cellular    cmp-swarm8  SERIAL  20    52231432  -66
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:45.480433252Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     17493510  -92
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:45.480433252Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     52062722  -93
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:47.880121714Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     20936200  -81
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:15:08.080783188Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     51782144  -109
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T07:52:58.880215896Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     18883589  -65
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:12.480346879Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17643520  -58
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T07:41:38.580213076Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     19366661  -73
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:56.980995646Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17463813  -67
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:32.580391463Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     19529225  -80
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:58.480748245Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     19771648  -71
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:26.280261451Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     18105604  -69
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:52.980138583Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17041670  -96
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:42.880515318Z  cellular      4g       cellular    cmp-swarm8  SERIAL  3     17784320  -99
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:33.98076754Z   cellular      4g       cellular    cmp-swarm8  SERIAL  7     17268483  -87
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:55.180421457Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     20156423  -84
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:16.480645054Z  cellular      4g       cellular    cmp-swarm8  SERIAL  7     16977664  -83
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:27.08020488Z   cellular      4g       cellular    cmp-swarm8  SERIAL  3     20038916  -80
--- 49
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  band  rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:11.251029726Z  cellular      5g       cellular    cmp-swarm7  SERIAL  78    -86
--- 50
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:26.350577705Z  cellular      5g       cellular    cmp-swarm7  SERIAL  -80
--- 51
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  band  rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:14.450757421Z  cellular      5g       cellular    cmp-swarm7  SERIAL  78    -92
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:00:42.85082825Z   cellular      5g       cellular    cmp-swarm7  SERIAL  78    
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:19.150766623Z  cellular      5g       cellular    cmp-swarm7  SERIAL        -32768
--- 53
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:18:54.051525522Z  cellular      5g       cellular    cmp-swarm7  SERIAL  -32768
--- 54
_start                          _stop                           _time                           _measurement  carrier  event_type  host        serial_number  band  rsrp
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:08.880341488Z  cellular      5g       cellular    cmp-swarm8  SERIAL  78    -91
2021-11-18T07:19:47.422072723Z  2021-11-18T08:19:47.422072723Z  2021-11-18T08:19:45.480433252Z  cellular      5g       cellular    cmp-swarm8  SERIAL  78    -97

Hello @eriktar,
Try adding a group() before the fieldsAsCols()

band_union = union(
    tables: [band_4G,band_5G]
    )
|> group()
 |> v1.fieldsAsCols()