cancel
Showing results for
Did you mean:
Creator

## Having to find values in previous rows

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

Labels (5)

• ### values in previous rows

2 Solutions

Accepted Solutions
Partner - Contributor III

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:
Date,
Reg_No,
Litres_Issued as Litres_Issued,
Odo as Odo
FROM
[Map1.xlsx]
(ooxml, embedded labels, table is Blad1)
;

Transactions:
*,
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

Creator
Author

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

3 Replies
Partner - Contributor III

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:
Date,
Reg_No,
Litres_Issued as Litres_Issued,
Odo as Odo
FROM
[Map1.xlsx]
(ooxml, embedded labels, table is Blad1)
;

Transactions:
*,
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

Creator
Author

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

Creator
Author

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

Community Browser