topic Remove first row when using peek() function in data load in QlikView

Remove first row when using peek() function in data load

varmekontrol — Sat, 16 Nov 2024 21:08:29 GMT

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

Re: Remove first row when using peek() function in data load

VishalWaghole — Mon, 01 Apr 2019 18:07:12 GMT

Try this,

If(RowNo() > 1, peek('Water_m3')) As Water_m3_peek

Re: Remove first row when using peek() function in data load

varmekontrol — Mon, 01 Apr 2019 18:11:48 GMT

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

Re: Remove first row when using peek() function in data load

varmekontrol — Mon, 01 Apr 2019 18:26:20 GMT

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

Re: Remove first row when using peek() function in data load

varmekontrol — Mon, 01 Apr 2019 18:39:32 GMT

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;