topic Re: Having to find values in previous rows in QlikView

Having to find values in previous rows

jacqueshol — Sat, 16 Nov 2024 19:54:24 GMT

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

Re: Having to find values in previous rows

Tim_Wensink — Mon, 14 Oct 2019 11:18:41 GMT

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

Re: Having to find values in previous rows

jacqueshol — Mon, 14 Oct 2019 12:39:15 GMT

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

Re: Having to find values in previous rows

jacqueshol — Mon, 14 Oct 2019 13:16:12 GMT

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