@rstuff When you pivot the data, rows where no light value was found should have a null light
column. You can use fill(column: "light", usePrevious:true)
grab the last non-null value in that column and use it to fill the value.
For example, when you pivot your data, I’m guessing it looks something like this:
_time |
light |
temp |
pressure |
2021-01-01T00:00:00Z |
on |
70.1 |
30.1 |
2021-01-01T00:05:00Z |
|
70.1 |
30.1 |
2021-01-01T00:10:00Z |
|
70.5 |
30.5 |
2021-01-01T00:15:00Z |
off |
70.4 |
30.4 |
2021-01-01T00:20:00Z |
|
70.1 |
30.1 |
2021-01-01T00:25:00Z |
|
70.1 |
30.1 |
2021-01-01T00:30:00Z |
off |
70.5 |
30.5 |
2021-01-01T00:35:00Z |
|
70.4 |
30.4 |
Note: You may need to sort your data by time after pivot()
. It doesn’t always guarantee sort order.
You can use fill(column: "light", usePrevious: true)
to get the following output:
_time |
light |
temp |
pressure |
2021-01-01T00:00:00Z |
on |
70.1 |
30.1 |
2021-01-01T00:05:00Z |
on |
70.1 |
30.1 |
2021-01-01T00:10:00Z |
on |
70.5 |
30.5 |
2021-01-01T00:15:00Z |
off |
70.4 |
30.4 |
2021-01-01T00:20:00Z |
off |
70.1 |
30.1 |
2021-01-01T00:25:00Z |
off |
70.1 |
30.1 |
2021-01-01T00:30:00Z |
off |
70.5 |
30.5 |
2021-01-01T00:35:00Z |
off |
70.4 |
30.4 |
Here’s a full, functional example:
import "csv"
csvData = "
#group,false,false,false,true,false
#datatype,string,long,dateTime:RFC3339,string,string
#default,_result,,,,
,result,table,_time,_field,_value
,,0,2021-01-01T00:00:00Z,light,on
,,0,2021-01-01T00:15:00Z,light,off
,,0,2021-01-01T00:30:00Z,light,off
#group,false,false,false,true,false
#datatype,string,long,dateTime:RFC3339,string,double
#default,_result,,,,
,result,table,_time,_field,_value
,,1,2021-01-01T00:00:00Z,temp,70.1
,,1,2021-01-01T00:05:00Z,temp,70.1
,,1,2021-01-01T00:10:00Z,temp,70.5
,,1,2021-01-01T00:15:00Z,temp,70.4
,,1,2021-01-01T00:20:00Z,temp,70.1
,,1,2021-01-01T00:25:00Z,temp,70.1
,,1,2021-01-01T00:30:00Z,temp,70.5
,,1,2021-01-01T00:35:00Z,temp,70.4
,,2,2021-01-01T00:00:00Z,pressure,30.1
,,2,2021-01-01T00:05:00Z,pressure,30.1
,,2,2021-01-01T00:10:00Z,pressure,30.5
,,2,2021-01-01T00:15:00Z,pressure,30.4
,,2,2021-01-01T00:20:00Z,pressure,30.1
,,2,2021-01-01T00:25:00Z,pressure,30.1
,,2,2021-01-01T00:30:00Z,pressure,30.5
,,2,2021-01-01T00:35:00Z,pressure,30.4
"
csv.from(csv: csvData)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> sort(columns: ["_time"])
|> fill(column: "light", usePrevious: true)