Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

varmekontrol
Contributor

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

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_noMeter_noCreatedWater_m3Water_m3_consumption
6037632989680129-03-2019 21:0026347,040,04
6037632989680129-03-2019 21:3026347,120,08
6037632989680129-03-2019 22:0026347,170,05
6037632989680129-03-2019 22:3026347,230,06
6037632989680129-03-2019 23:0026347,290,06
6037632989680129-03-2019 23:3026347,350,06
6037632989680130-03-2019 00:0026347,410,06
6037632989740125-03-2019 00:0051793,6525446,24
6037632989740125-03-2019 00:0051793,650
6037632989740125-03-2019 00:3051793,740,09
6037632989740125-03-2019 00:3051793,740
6037632989740125-03-2019 01:0051793,820,08

 

Labels (2)
1 Solution

Accepted Solutions
varmekontrol
Contributor

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

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;
4 Replies
Highlighted
VishalWaghole
Valued Contributor II

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

Try this,

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

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

Perfect.. 

Thank you 

6037632989680129-03-2019 23:3023.30.0029-03-201926347,350,06
6037632989680130-03-2019 00:0000.00.0030-03-201926347,410,06
6037632989740125-03-2019 00:0000.00.0025-03-201951793,650
6037632989740125-03-2019 00:3000.30.0025-03-201951793,740,09
6037632989740125-03-2019 00:3000.30.0025-03-201951793,740
varmekontrol
Contributor

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

@VishalWaghole  sorry to take the solution from you, but it keeps coming back. I have no idea why. 

Cme_noMeter_noCreatedWater_m3Water_m3_peekWater_m3_consumption
6037632989680129-03-2019 23:0026347,2926347,230,06
6037632989680129-03-2019 23:3026347,3526347,290,06
6037632989680130-03-2019 00:0026347,4126347,350,06
6037632989740125-03-2019 00:0051793,6526347,4125446,24
6037632989740125-03-2019 00:0051793,6551793,650
6037632989740125-03-2019 00:3051793,7451793,650,09
6037632989740125-03-2019 00:3051793,7451793,740
6037632989740125-03-2019 01:0051793,8251793,740,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;
varmekontrol
Contributor

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

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;