Hello @Anaisdg , no problem, here what you have requested.
My objective is to put ‘null’ value on my calculation, if field “_max” does not exist or if the gap between max and min is 0 (next step 
).
Why I want null value ?
I would like to put null because when we are using 'wideTo" function, no record created if null value.
It will help to minimize a lot the data volume (I got around 1million data point per days ).
The aim is to put ‘Null’ on the else result instead of ‘0.0’
Here an extract of data
Result will be 3 rows, you will see some max value with null and the result conso is 0 (but I would like null)
Data_Input - 2022-10-08_10_30_influxdb_data.txt (2.9 MB)
Here the full query
import "math"
import "timezone"
option location = timezone.location(name: "Europe/Paris")
data = () =>
from(bucket: "Jeedom")
// |> range(start: -5d)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(
fn: (r) =>
r["_measurement"] == "BBRHCJB" or r["_measurement"] == "BBRHCJR" or r["_measurement"] == "BBRHCJW"
or
r["_measurement"] == "BBRHPJB" or r["_measurement"] == "BBRHPJR" or r["_measurement"] == "BBRHPJW"
or
r["_measurement"] == "HCHC" or r["_measurement"] == "HCHP",
)
|> filter(fn: (r) => r["_field"] == "Compteur")
max = data()
|> aggregateWindow(every: 1d, fn: max, createEmpty: false)
|> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
|> rename(
fn: (column) => {
columnRegex = /HCHC|HCHP|BBRHCJB|BBRHCJW|BBRHCJR|BBRHPJB|BBRHPJW|BBRHPJR/
columnName = if column =~ columnRegex then "${column}_Max" else column
return columnName
},
)
min = data()
|> aggregateWindow(every: 1d, fn: min, createEmpty: false)
|> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
|> rename(
fn: (column) => {
columnRegex = /HCHC|HCHP|BBRHCJB|BBRHCJW|BBRHCJR|BBRHPJB|BBRHPJW|BBRHPJR/
columnName = if column =~ columnRegex then "${column}_Min" else column
return columnName
},
)
join(tables: {min: min, max: max}, on: ["_time"])
|> drop(columns: ["_field_max","_field_min","_start_max","_start_min","_stop_max","_stop_min"])
|> map(
fn: (r) => ({ r with
BRHCJB_Conso: if exists r.BBRHCJB_Max then math.roundtoeven(x: (r.BBRHCJB_Max - r.BBRHCJB_Min) / 10.0) / 100.0 else 0.0,
BRHPJB_Conso: if exists r.BBRHPJB_Max then math.roundtoeven(x: (r.BBRHPJB_Max - r.BBRHPJB_Min) / 10.0) / 100.0 else 0.0,
BRHCJW_Conso: if exists r.BBRHCJW_Max then math.roundtoeven(x: (r.BBRHCJW_Max - r.BBRHCJW_Min) / 10.0) / 100.0 else 0.0,
BRHPJW_Conso: if exists r.BBRHPJW_Max then math.roundtoeven(x: (r.BBRHPJW_Max - r.BBRHPJW_Min) / 10.0) / 100.0 else 0.0,
BRHCJR_Conso: if exists r.BBRHCJR_Max then math.roundtoeven(x: (r.BBRHCJR_Max - r.BBRHCJR_Min) / 10.0) / 100.0 else 0.0,
BRHPJR_Conso: if exists r.BBRHPJR_Max then math.roundtoeven(x: (r.BBRHPJR_Max - r.BBRHPJR_Min) / 10.0) / 100.0 else 0.0,
HCHC_Conso: if exists r.HCHC_Max then math.roundtoeven(x: (r.HCHC_Max - r.HCHC_Min) / 10.0) / 100.0 else 0.0,
HCHP_Conso: if exists r.HCHP_Max then math.roundtoeven(x: (r.HCHP_Max - r.HCHP_Min) / 10.0) / 100.0 else 0.0,
}),
)
|> map(
fn: (r) => ({ r with
STAT_TODAY_HP: math.roundtoeven(x: (r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso) * 100.0) / 100.0,
STAT_TODAY_HC: math.roundtoeven(x: (r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso) * 100.0) / 100.0,
STAT_TODAY:
(math.round(x:
((math.roundtoeven(x: (r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso) * 100.0) / 100.0 +
math.roundtoeven(x: (r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso) * 100.0) / 100.0)) * 100.0) / 100.0
) ,
}),
)
|> keep(fn: (column) => column =~ /_Conso|STAT_TODAY|_time|_Max|_Min/)
// Creation _measurement pour utiliser la fonction wideTo
|> set(key: "_measurement", value: "Conso")
|> group(columns: ["_measurement"])
// Ecriture dans le Bucket Jeedom_History
|> wideTo(bucket: "Jeedom_History")
