Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
EDIT: Apologies if this post appears twice, but it seems my first post was reported as spam and I can't find it under my profile. I am really in need of assistance with the question below.
While I am not new to QlikView (still using QlikView 11), 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
Hello Jacques,
I would do this as follows:
- Read the Excel sheet into Qlikview (say table tmpTransactions)
- Read from this temporary table into a new table Transactions, order by reg_no and date (you can only do this from a resident table, not from an excel file)
- For each line, determine if the previous line contains data about the same reg_no, if so, calculate the difference in kms, and divide this by the no of litres.
The script I used:
tmpTransactions:
LOAD Trans_No,
Date,
Reg_No,
Litres_Issued as Litres_Issued,
Odo as Odo
FROM
[Map1.xlsx]
(ooxml, embedded labels, table is Blad1)
;
Transactions:
noconcatenate load
*,
if (Previous (Reg_No) = Reg_No, Odo - previous (Odo)) as [No_of_kms],
if (Previous (Reg_No) = Reg_No, (Odo - previous (Odo)) / Litres_Issued ) as [No_of_kms_per_litre]
resident tmpTransactions
order by Reg_No, Date asc;
drop table tmpTransactions;
If I do this, I get a table as follows:
As you can see, line number 8 contains 199 litres and 1,46 km / litre.
Hope to have helped you,
Tim Wensink
Hi Tim,
Thank you kindly for your response. I will most definitely be trying that out a little later today. The challenge for me would be to try and understand why that works... 🙂
Cheers,
Jacques
Hello Jacques,
I would do this as follows:
- Read the Excel sheet into Qlikview (say table tmpTransactions)
- Read from this temporary table into a new table Transactions, order by reg_no and date (you can only do this from a resident table, not from an excel file)
- For each line, determine if the previous line contains data about the same reg_no, if so, calculate the difference in kms, and divide this by the no of litres.
The script I used:
tmpTransactions:
LOAD Trans_No,
Date,
Reg_No,
Litres_Issued as Litres_Issued,
Odo as Odo
FROM
[Map1.xlsx]
(ooxml, embedded labels, table is Blad1)
;
Transactions:
noconcatenate load
*,
if (Previous (Reg_No) = Reg_No, Odo - previous (Odo)) as [No_of_kms],
if (Previous (Reg_No) = Reg_No, (Odo - previous (Odo)) / Litres_Issued ) as [No_of_kms_per_litre]
resident tmpTransactions
order by Reg_No, Date asc;
drop table tmpTransactions;
If I do this, I get a table as follows:
As you can see, line number 8 contains 199 litres and 1,46 km / litre.
Hope to have helped you,
Tim Wensink
Hi Tim,
Thank you kindly for your response. I will most definitely be trying that out a little later today. The challenge for me would be to try and understand why that works... 🙂
Cheers,
Jacques
Hi Tim,
I have tried your solution and it works a charm. Thank you so much. What is really scary, is that I actually understood what you have done. A rather elegant solution.
Thanks again,
Jacques