Use of Peek, Previous

[jacqueshol]

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

[Arthur_Fong]

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

[JGMDataAnalysis · Accepted solution]

Temp:
NOCONCATENATE 
LOAD *
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;

[jacqueshol]

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