Calculate difference in sum based on previous day, week, month,year
Hi
I need help on how to find the consumption of a heat meter based on the previous date.
My Data
Meter No]
Created
Energy (wH)
What i need
82251145
17-03-2019
100
82251145
18-03-2019
250
150
82251145
19-03-2019
500
250
82251145
20-03-2019
1200
700
82251145
21-03-2019
5000
3800
82251145
22-03-2019
6000
1000
82251145
23-03-2019
8500
2500
82251145
24-03-2019
9500
1000
My load.
Heat_meter:
LOAD Num(@2) as [Meter No], //Heat meter serial no.
Date(@3) as [Created], //Timestamp
Num(@7) as [Energy (wH)] //Actual energy used since the day it was installed.
//What i need is to subtract the Energy from the previous day, to get the consumption used the last day.
FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
Heat_meter:
LOAD
Num(@2) as [Meter No], //Heat meter serial no.
Date(@3) as [Created], //Timestamp
Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed.
Num(@7 - peek('@7') as [What i need]
FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
Heat_meter:
LOAD
Num(@2) as [Meter No], //Heat meter serial no.
Date(@3) as [Created], //Timestamp
Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed.
Num(@7 - peek('@7') as [What i need]
FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
Heat_meter:
LOAD
Num(@2) as [Meter No], //Heat meter serial no.
Date(@3) as [Created], //Timestamp
Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed.
Num(@7 - peek('@7') as [What i need]
FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
(Jeg siger tak for hjælpen, det var lige det jeg skulle bruge. :))
Just a msg. for my Scandinavian buddy 🙂 The Peek() was just what i needed.
This is my result if someone else needs the solution.
Heat_meter_temp:
LOAD Cme_no,
Meter_no,
date(Created, 'YYYY-MM-DD HH:MM:ss') as Created,
num(Energy_wH/1000) as Energy_wH,
Volume_m3,
Volume_flow_m3H,
Flow_temp_C,
Return_temp_C,
Temp_diff_C
FROM
[C:\Heat_meter.qvd]
(qvd);
Heat_meter:
NoConcatenate
LOAD *,
peek('Energy_wH') as Energy_wH_peek,
((Energy_wH-peek('Energy_wH'))) as Energy_wH_consumption,
peek('Volume_m3') as Volume_m3_peek,
num(((Volume_m3-peek('Volume_m3'))),'##,00') as Volume_m3_consumption
Resident
Heat_meter_temp order by Meter_no,Created;
Drop Field Energy_wH_peek, Volume_m3_peek;
Drop Table Heat_meter_temp;
