Calculate difference between each running timestamp

[varmekontrol]

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

[Gysbert_Wassenaar]

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
]; 

[varmekontrol]

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
]; 

[cengizeralp]

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
];