Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jacqueshol
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_NoDateReg_NoLitres_IssuedOdo
101-Aug-2019CP 44 DZ GP414   500,616
201-Aug-2019JH 21 FG GP176  488,086
302-Aug-2019CP 44 DZ GP360  500,976
402-Aug-2019JH 21 FG GP275  488,489
502-Aug-2019BYZ 652 GP163  24,193
603-Aug-2019CP 44 DZ GP373  501,405
703-Aug-2019JH 21 FG GP328  488,896
803-Aug-2019BYZ 652 GP136  24,392
904-Aug-2019CP 44 DZ GP385  501,815
1004-Aug-2019JH 21 FG GP371  489,326
1104-Aug-2019BYZ 652 GP197  24,663
1205-Aug-2019JH 21 FG GP187  489,422
1305-Aug-2019CP 44 DZ GP310  502,149
1405-Aug-2019JH 21 FG GP213  489,592
1506-Aug-2019JH 21 FG GP200  489,831
1606-Aug-2019BYZ 652 GP124  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

 

1 Solution

Accepted Solutions
JGMDataAnalysis
Contributor II

Re: Use of Peek, Previous

 

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;

 

 

clipboard_image_1.png

 

View solution in original post

3 Replies
Partner
Partner

Re: Use of Peek, Previous

There you go:

clipboard_image_0.png

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
Contributor II

Re: Use of Peek, Previous

 

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;

 

 

clipboard_image_1.png

 

View solution in original post

jacqueshol
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