Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am using peek() to calculate the consumption for the meter_no (heat meters)
Water_meter_temp: LOAD Cme_no, Meter_no, date(Created, 'YYYY-MM-DD hh:mm') as Created, time(Created) as Created_time, dayname(Created) as Created_dayname, Water_m3 FROM [C:\temp\Teknikrum\QVD\Water_meter.qvd] (qvd); Water_meter_temp_1: NoConcatenate LOAD *, peek('Water_m3') as Water_m3_peek, num(((Water_m3-peek('Water_m3'))),'#####,00') as Water_m3_consumption Resident Water_meter_temp order by Meter_no,Created;
It almost works perfectly.
Except when the Meter_no changes to another, it does not start the peek() from that point but just continues.
I have highlighted where it goes wrong in the table. The Meter_no and date change, but the peek() just continues and calculates a crazy high number.
Cme_no | Meter_no | Created | Water_m3 | Water_m3_consumption |
6037632 | 9896801 | 29-03-2019 21:00 | 26347,04 | 0,04 |
6037632 | 9896801 | 29-03-2019 21:30 | 26347,12 | 0,08 |
6037632 | 9896801 | 29-03-2019 22:00 | 26347,17 | 0,05 |
6037632 | 9896801 | 29-03-2019 22:30 | 26347,23 | 0,06 |
6037632 | 9896801 | 29-03-2019 23:00 | 26347,29 | 0,06 |
6037632 | 9896801 | 29-03-2019 23:30 | 26347,35 | 0,06 |
6037632 | 9896801 | 30-03-2019 00:00 | 26347,41 | 0,06 |
6037632 | 9897401 | 25-03-2019 00:00 | 51793,65 | 25446,24 |
6037632 | 9897401 | 25-03-2019 00:00 | 51793,65 | 0 |
6037632 | 9897401 | 25-03-2019 00:30 | 51793,74 | 0,09 |
6037632 | 9897401 | 25-03-2019 00:30 | 51793,74 | 0 |
6037632 | 9897401 | 25-03-2019 01:00 | 51793,82 | 0,08 |
Found a solution. were previous() came into play.
Water_meter_temp_1: NoConcatenate LOAD *, if(rowno()>1, peek('Water_m3')) as Water_m3_peek, if(Cme_no = Previous(Cme_no) and Meter_no = Previous(Meter_no), num(((Water_m3-peek('Water_m3'))),'#####,00'), Num(0,'#####,00')) as Water_m3_consumption Resident Water_meter_temp order by Meter_no,Created;
Perfect..
Thank you
6037632 | 9896801 | 29-03-2019 23:30 | 23.30.00 | 29-03-2019 | 26347,35 | 0,06 |
6037632 | 9896801 | 30-03-2019 00:00 | 00.00.00 | 30-03-2019 | 26347,41 | 0,06 |
6037632 | 9897401 | 25-03-2019 00:00 | 00.00.00 | 25-03-2019 | 51793,65 | 0 |
6037632 | 9897401 | 25-03-2019 00:30 | 00.30.00 | 25-03-2019 | 51793,74 | 0,09 |
6037632 | 9897401 | 25-03-2019 00:30 | 00.30.00 | 25-03-2019 | 51793,74 | 0 |
@VishalWaghole sorry to take the solution from you, but it keeps coming back. I have no idea why.
Cme_no | Meter_no | Created | Water_m3 | Water_m3_peek | Water_m3_consumption |
6037632 | 9896801 | 29-03-2019 23:00 | 26347,29 | 26347,23 | 0,06 |
6037632 | 9896801 | 29-03-2019 23:30 | 26347,35 | 26347,29 | 0,06 |
6037632 | 9896801 | 30-03-2019 00:00 | 26347,41 | 26347,35 | 0,06 |
6037632 | 9897401 | 25-03-2019 00:00 | 51793,65 | 26347,41 | 25446,24 |
6037632 | 9897401 | 25-03-2019 00:00 | 51793,65 | 51793,65 | 0 |
6037632 | 9897401 | 25-03-2019 00:30 | 51793,74 | 51793,65 | 0,09 |
6037632 | 9897401 | 25-03-2019 00:30 | 51793,74 | 51793,74 | 0 |
6037632 | 9897401 | 25-03-2019 01:00 | 51793,82 | 51793,74 | 0,08 |
Water_meter: NoConcatenate LOAD *, if(rowno()>1, peek('Water_m3')) as Water_m3_peek, num(((Water_m3-peek('Water_m3'))),'#####,00') as Water_m3_consumption Resident Water_meter_temp order by Meter_no,Created;
Found a solution. were previous() came into play.
Water_meter_temp_1: NoConcatenate LOAD *, if(rowno()>1, peek('Water_m3')) as Water_m3_peek, if(Cme_no = Previous(Cme_no) and Meter_no = Previous(Meter_no), num(((Water_m3-peek('Water_m3'))),'#####,00'), Num(0,'#####,00')) as Water_m3_consumption Resident Water_meter_temp order by Meter_no,Created;