How to calculate kilometer consumed through odometer?

[#1]

Hi,

What if you have this datasource

Date               Vehicle          Odometer

3/12/2012          A                    12345

3/12/2012          A                    20001

3/12/2012          B                    43556

3/13/2012          B                    49909

Can we able to get the kilometer travel per Vehicle? A and B?

Because, in my datasource, I only have one Odometer Reading, I'm not sure if it is reset to ZERO everytime it's newly fueled.

Thanks,

Bill         

[jstensig]

Hi,
Take a look at the Previous-function and use it in a resident load. Make a Resident-load on the initial data and remember to sort your data.

Syntax (UNTESTED)

// Get data
Initial_load:
LOAD Date, Vehicle, Odometer
FROM [datasource];

// Resident load, where you sort the data by Vehicle and Odometer AND uses the Previous-function to calculate the difference (trip)

Data:
NoConcatenate
LOAD *
,if(Vehicle=Previous(Vehicle),Odometer-Previous(Odometer),null()) AS Trip
RESIDENT Initial_load
ORDER BY Vehicle, Odometer;

// Drop initial table

DROP TABLE Initial_load;

[#3]

Thanks for the idea.. Can u have an example?

[jstensig]

try the above syntax and see if it works 🙂