Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

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

2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

Re: Having to find values in previous rows

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

Highlighted
Contributor

Re: Having to find values in previous rows

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
Highlighted
Partner
Partner

Re: Having to find values in previous rows

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

Highlighted
Contributor

Re: Having to find values in previous rows

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

Highlighted
Contributor

Re: Having to find values in previous rows

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