Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jacqueshol
Creator
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)
2 Solutions

Accepted Solutions
Tim_Wensink
Partner - Contributor III
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:
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:

clipboard_image_0.png

As you can see, line number 8 contains 199 litres and 1,46 km / litre.

Hope to have helped you,


Tim Wensink

 

View solution in original post

jacqueshol
Creator
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

View solution in original post

3 Replies
Tim_Wensink
Partner - Contributor III
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:
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:

clipboard_image_0.png

As you can see, line number 8 contains 199 litres and 1,46 km / litre.

Hope to have helped you,


Tim Wensink

 

jacqueshol
Creator
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

jacqueshol
Creator
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