Union - only one table taken into account by result

InfluxDB 2
1

I would like to do union of 2 results to be able to do cost calculation.
I already used this kind of query but for this one the result is not the union of the 2 tables.
I will use sort() and fill() function to be able to calculate.

It looks that only one table is taken into account the union function, I do not know how to debug it.

1 - Select cost

import "timezone"
option location = timezone.location(name: "Europe/Paris")

Cout = from(bucket: "Jeedom") 
  |> range(start: -1y)
  |> filter(fn: (r) => r["_measurement"] =~ /Cout/)
  |> filter(fn: (r) => r["_field"] == "Prix_kw")
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
  |> keep(fn: (column) => column =~ /_time|Cout/)
  |> yield(name: "Cost")

image
image1792×127 14 KB

2 - Select Data consumtion

Conso = from(bucket: "Jeedom_History")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Conso")
    |> filter(fn: (r) => r["_field"] =~ /_time|Conso|STAT_TODAY/)
    |> drop(columns: ["_measurement"])
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> yield(name: "Conso")

image
image1802×117 13.7 KB

3 - The Union

 union(tables: [Cout, Conso])

The result is not what it should be, it looks that only Cost table is taken into account.
The columns of the Conso table are not added neither the data

image
image1789×126 13.8 KB

2

I have done quick added test with limited of rows and columns
And unfortunately, the union is not a merge of data and columns but again the _RESULT Table is a list of 2 tables without merging the table column structure of STAT & TARIF, of course the sort function is not taken into account also.
What is missing here, very simple and basic query using the same bucket, I do not understand.

That’s crazy, I do not know how to solve the issue.

image
image1783×557 46.8 KB 

import "timezone"
option location = timezone.location(name: "Europe/Paris")
 
stat = from(bucket: "Jeedom_History")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Conso")
    |> filter(fn: (r) => r["_field"] =~ /STAT_TODAY/)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> sort(columns: ["_time"])
    |> yield(name: "stat")

tarif = from(bucket: "Jeedom_History") 
  |> range(start: -1y)
  |> filter(fn: (r) => r["_measurement"] == "Tarif")
  |> filter(fn: (r) => r["_field"] =~ /Cout_Heure_Creuse/)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> sort(columns: ["_time"])
  |> keep(fn: (column) => column =~ /_time|Cout/)
  |> yield(name: "tarif")

union(tables: [stat, tarif])
 |> sort(columns: ["_time"])
3

I team,

I have done a new query with the exact same lines and all is working well.
I suspect special character part of my some copy/past commad udring my first creation.

All is good now

1 Like
4

Hello @Franck_Bellot,
Thanks for sharing your fix. For others stumbling into issues with union, consider applying a |> group() to the end of your two streams before joining them.

5

Thanks for this tip, I did not know it.