# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

## Use of Peek, Previous

Hi All,

While I am not new to QlikView, I am very much a newbie "developer". I would therefore appreciate any assistance.  I have the following extract (simplified version) of an Excel spreadsheet that depicts the fuel usage for three different vehicles.

 Trans_No Date Reg_No Litres_Issued Odo 1 01-Aug-2019 CP 44 DZ GP 414 500,616 2 01-Aug-2019 JH 21 FG GP 176 488,086 3 02-Aug-2019 CP 44 DZ GP 360 500,976 4 02-Aug-2019 JH 21 FG GP 275 488,489 5 02-Aug-2019 BYZ 652 GP 163 24,193 6 03-Aug-2019 CP 44 DZ GP 373 501,405 7 03-Aug-2019 JH 21 FG GP 328 488,896 8 03-Aug-2019 BYZ 652 GP 136 24,392 9 04-Aug-2019 CP 44 DZ GP 385 501,815 10 04-Aug-2019 JH 21 FG GP 371 489,326 11 04-Aug-2019 BYZ 652 GP 197 24,663 12 05-Aug-2019 JH 21 FG GP 187 489,422 13 05-Aug-2019 CP 44 DZ GP 310 502,149 14 05-Aug-2019 JH 21 FG GP 213 489,592 15 06-Aug-2019 JH 21 FG GP 200 489,831 16 06-Aug-2019 BYZ 652 GP 124 24,870

I am needing QlikView to calculate the difference in odometer readings (Odo field) by vehicle and subsequently calculate the fuel consumption in km / litre.

By example,

BYZ 652 GP's first fuel fill up was Trans No 5 (transaction id) with an odo reading of 24,193. It refueled as depicted in Trans No 8 with the odo reading on 24,392. Therefore, the kilometres travelled between these two fill-ups would be:  24,392 (current odo reading) - 24,193 (previous odo reading) = 199 kms and should be presented in a column on line 8.  The fuel consumption must then be calculated as follows:

Kilometres travelled / litres refueled = 199 kms / 136 litres = 1.46 km / l.  This needs to be in the next column on line 8.

I have obviously no idea how to do this.  I have read forums all morning on using the Peek and Previous functions, but to no avail. I have even looked at using the Above command, but couldn't get that to work either.

I therefore need a function in determining the difference in kilometres between fill-ups keeping in mind that the previous transaction for that particular vehicle won't necessarily be in the previous row.  The function would need to search for the previous fill-up, for that vehicle, that could be any number of transactions before the current one.

I would sincerely appreciate any assistance in this regard.

Thank you kindly,

Jacques

Labels (3)

• ### Previous

1 Solution

Accepted Solutions
Highlighted
Contributor II

## Re: Use of Peek, Previous

``````Temp:
NOCONCATENATE
INLINE [
Trans_No, Date,		   Reg_No,		Litres_Issued, Odo
1,		  01/08/2019,  CP 44 DZ GP,	414,	   	   500616
2,		  01/08/2019,  JH 21 FG GP,	176,	  	   488086
3,		  02/08/2019,  CP 44 DZ GP,	360,	  	   500976
4,		  02/08/2019,  JH 21 FG GP,	275,	  	   488489
5,		  02/08/2019,  BYZ 652 GP,	163,	  	   24193
6,		  03/08/2019,  CP 44 DZ GP,	373,	  	   501405
7,		  03/08/2019,  JH 21 FG GP,	328,	  	   488896
8,		  03/08/2019,  BYZ 652 GP,	136,	  	   24392
9,		  04/08/2019,  CP 44 DZ GP,	385,	  	   501815
10,		  04/08/2019,  JH 21 FG GP,	371,	  	   489326
11,	 	  04/08/2019,  BYZ 652 GP,	197,	  	   24663
12,		  05/08/2019,  JH 21 FG GP,	187,	  	   489422
13,	  	  05/08/2019,  CP 44 DZ GP,	310,	  	   502149
14,		  05/08/2019,  JH 21 FG GP,	213,	  	   489592
15,		  06/08/2019,  JH 21 FG GP,	200,	  	   489831
16,		  06/08/2019,  BYZ 652 GP,	124,	  	   24870
];

Test:
NOCONCATENATE
LOAD *, Round(Km_Traveled / Litres_Issued, 0.01) AS Fuel_Consumption
;
LOAD *, If(Peek(Reg_No) = Reg_No, Odo - Peek(Odo), Null()) AS Km_Traveled
RESIDENT Temp
ORDER BY Reg_No, Trans_No;

DROP TABLE Temp;``````

3 Replies
Highlighted
Partner

## Re: Use of Peek, Previous

There you go:

You have to sort the table by Reg_No and Date for peek to work well.

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

Highlighted
Contributor II

## Re: Use of Peek, Previous

``````Temp:
NOCONCATENATE
INLINE [
Trans_No, Date,		   Reg_No,		Litres_Issued, Odo
1,		  01/08/2019,  CP 44 DZ GP,	414,	   	   500616
2,		  01/08/2019,  JH 21 FG GP,	176,	  	   488086
3,		  02/08/2019,  CP 44 DZ GP,	360,	  	   500976
4,		  02/08/2019,  JH 21 FG GP,	275,	  	   488489
5,		  02/08/2019,  BYZ 652 GP,	163,	  	   24193
6,		  03/08/2019,  CP 44 DZ GP,	373,	  	   501405
7,		  03/08/2019,  JH 21 FG GP,	328,	  	   488896
8,		  03/08/2019,  BYZ 652 GP,	136,	  	   24392
9,		  04/08/2019,  CP 44 DZ GP,	385,	  	   501815
10,		  04/08/2019,  JH 21 FG GP,	371,	  	   489326
11,	 	  04/08/2019,  BYZ 652 GP,	197,	  	   24663
12,		  05/08/2019,  JH 21 FG GP,	187,	  	   489422
13,	  	  05/08/2019,  CP 44 DZ GP,	310,	  	   502149
14,		  05/08/2019,  JH 21 FG GP,	213,	  	   489592
15,		  06/08/2019,  JH 21 FG GP,	200,	  	   489831
16,		  06/08/2019,  BYZ 652 GP,	124,	  	   24870
];

Test:
NOCONCATENATE
LOAD *, Round(Km_Traveled / Litres_Issued, 0.01) AS Fuel_Consumption
;
LOAD *, If(Peek(Reg_No) = Reg_No, Odo - Peek(Odo), Null()) AS Km_Traveled
RESIDENT Temp
ORDER BY Reg_No, Trans_No;

DROP TABLE Temp;``````

Highlighted
Contributor

## Re: Use of Peek, Previous

Hi Arthur,

Thank you so much.  Your example file made me understand for the first time how to use those functions.

Regards and take care,

Jacques