Hello,
I have a task extracting some data which is run periodically due to its size.
Basically i’m creating a discharge and a charge power/energy over a period of time out of voltage and current (and its sign) which i would like to then push into a bucket so that grafana can just query that simple bucket
Of course once data is pivoted, the “to” instruction complaints about the fact that it misses a lot of columns such as “_time”, “_field”, “_measurements”.
Ideally i should have a “_field” column under which i differentiate between Discharge_Power and Charge_Power whose value is stored in “_value”.
Unfortuately i’m failing to achieved that and for whatever reason even if “import experimental” and use it as “experimental.unpivot()” it complains about it not being valid apparently.
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Rome")
option task = {name: "Monthly Battery Power", cron: "0 5 * * *"}
array = [
"Jan",
"Feb",
"Mar",
"Apr",
"May",
"Jun",
"Jul",
"Aug",
"Sep",
"Oct",
"Nov",
"Dec",
]
A =
from(bucket: "PVdb")
|> range(start: -15d)
|> filter(fn: (r) => r._measurement == "bms" and r._field == "I" or r._field == "V")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
B =
A
|> map(fn: (r) => ({r with _value: if r.I >= 0 and r.I <= 100 then r.V * r.I else 0.0}))
|> window(every: 1mo)
|> integral(unit: 1h)
|> map(fn: (r) => ({r with _name: array[date.month(t: r._start) - 1]}))
|> group()
|> drop(columns: ["_field", "_measurement"])
C =
A
|> map(fn: (r) => ({r with _value: if r.I <= 0 and r.I >= -50 then (-r.V) * r.I else 0.0}))
|> window(every: 1mo)
|> integral(unit: 1h)
|> map(fn: (r) => ({r with _name: array[date.month(t: r._start) - 1]}))
|> group()
|> drop(columns: ["_field", "_measurement"])
join(tables: {Charge: B, Discharge: C}, on: ["_start", "_name", "_stop"], method: "inner")
|> map(fn: (r) => ({r with _time: r._start}))
|> to(bucket: .......)
