Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table with timestamp 'YYYY-MM-DD HH:mm:ss' and some measurements.
I would like to load the difference between the measurements from one timestamp to another.
In my example, I just use the dates, as it is faster 🙂
load * inline [
timestamp, measurement
2020-01-01, 10
2020-01-02, 30
2020-01-03, 80
2020-01-04, 110
2020-01-05, 150
2020-01-06, 500
];
What I am looking for is to load a table that now looks like this, where the difference is being calculated between all timestamps chronological.
timestamp | measurement | consumption |
2020-01-01 | 10 | 10 |
2020-01-02 | 30 | 20 |
2020-01-03 | 80 | 50 |
2020-01-04 | 110 | 30 |
2020-01-05 | 150 | 40 |
2020-01-06 | 500 | 350 |
Perhaps like this:
load *, alt(measurement-previous(measurement),measurement) as consumption inline [
timestamp, measurement
2020-01-01, 10
2020-01-02, 30
2020-01-03, 80
2020-01-04, 110
2020-01-05, 150
2020-01-06, 500
];
OKay, did not think it was that simple 🙂
What if I wanted to add a field I forgot. I have the ID were I want to calculate the difference between the two measurements between every date.
Maybe it would help if I told you it was a heat meter for measurement heat. The values from this heat meter are accumulating, so I want to know what this heat meter uses every day.
load *
inline [
id,timestamp, measurement
550055,2020-01-01, 10
550055,2020-01-02, 30
550055,2020-01-03, 80
550055,2020-01-04, 110
550055,2020-01-05, 150
550055,2020-01-06, 500
225500,2020-01-01, 8
225500,2020-01-02, 11
225500,2020-01-03, 40
225500,2020-01-04, 90
225500,2020-01-05, 50
225500,2020-01-06, 3
];
I think, this may solve your problem
load *
,if(peek(id)=id, alt(measurement-previous(measurement),measurement),measurement) as consumption
inline [
id,timestamp, measurement
550055,2020-01-01, 10
550055,2020-01-02, 30
550055,2020-01-03, 80
550055,2020-01-04, 110
550055,2020-01-05, 150
550055,2020-01-06, 500
225500,2020-01-01, 8
225500,2020-01-02, 11
225500,2020-01-03, 40
225500,2020-01-04, 90
225500,2020-01-05, 50
225500,2020-01-06, 3
];