Script to Calculate Total Km Travelled

[RacingLists]

I have a log of km entries and date and time of fillup that will allow a calculation of total miles travelled, total fuel used, and fuel efficiency for an motorcycle endurance race.

What I want FMPro to do is take the km for the first and last entry (last is the finish) and subtract to get the total km travelled. There are at least 100 entries for each motorcycle for the entire endurance race and about 200 motorcycles.

So entries look like:

1st Entry for Bike 1 odometer (5,786 km) : volume of fuel (13.4 litres) : date and time of fillup (05/12/05 15:10)

Many intermediate entries

Last Entry for Bike 1 (FINISH): odometer (8,580 km) : volume of fuel (0 litres) : date and time of fillup (05/21/05 02:54) which is actually the finish.

How would I isolate the km and volume of fuel in the first and last entry for the simple calculations that would follow? Do I need to eliminate the time entry in the cell? I could do that in a text editor quite easily.

I can't script at this level (I'm a biologist!), need some help! Thanks!

Edited April 24, 2006 by Guest

[LaRetta]

No script required. I'm a bit unclear on part of your thinking and I don't know what fuel efficiency means but I assume it's like your overall mileage?

1) The date shouldn't matter it seems ... at end of race total miles travelled and total fuel consumed?

2) A natural relationship to the same file will grab the first biker record. And Last() can grab the end.

Div ( Last ( BikeRace 2::Odometer ) - BikeRace 2::Odometer ; Sum ( BikeRace 2::VolumeOfFuel ) )

I've attached a file which might do the trick but I'm unsure which way to divide for consumption. So either this bike got great mileage or terrible mileage.

Note this relationship is in natural creation order so the first related record will always be the biker's start. If entries can be out of date order, then you should sort the relationship by date (ascending). And you may have to tweak my math ... I've never played with kilometer and gas mileage stuff.

LaRetta

BikeRace.zip

[swf]

I have a similar solution based on relationships.

It requires the use of a refresh script to redo all the calculations when new data is added.

I would like to improve it in 2 ways.

1) refresh without having to use the script

2) set up another TO with relationship to isolate the finish line entries. (the maximum of the timestamp and the odometer.)

For reporting purpose the user wants each bike number and the overall mpg. Each and every data point is not helpful in the final report.

I am sure someone can easily fix this up.

bike_Copy_1.fp7.zip

[LaRetta]

Oh. So it needs to count days lapsed between start and finish and get consumption from that? Still, no script necessary and only one calc.

Div ( Div ( Last ( BikeRace 2::Odometer ) - BikeRace 2::Odometer ; Sum ( BikeRace 2::VolumeOfFuel ) ) ; GetAsDate ( Last ( BikeRace 2::FillupTimeStamp ) - BikeRace 2::FillupTimeStamp ) )

It will give the Bike Number and overall mpg (I think) per race. racinglists knows the math ... he just didn't know the relationship-isolation portion of grabbing the head and the tail per biker.

L

[comment]

A few notes:

1. I don't know if there's a way to measure the amount of fuel in the tank of a bike. I would think the entries represent the fuel intake, taken from the pump reading, and that the first fuelling happens just before the race (i.e. the race starts with a full tank). Then, at the end of the race, the bikes are filled up again.

If so, the first fuelling represents the state of the tank BEFORE the race started, and should be subtracted from the total of fuel consumption.

2. The dates are irrelevant (unless you want to know the average DAILY consumption).

3. A minor point, but integer division (Div() function) is not quite appropriate here: a regular division should be used instead.

So the calculation would be:

Let ( [

distance = Last ( Entries::Odometer ) - Entries::Odometer ;

fuel = Sum ( Entries::FuelIntake ) - Entries::FuelIntake

] ;

distance / fuel

)

This returns the average fuel consumption in miles per gallon (or kilometers per litre, or whatever units were used in Entries). To get gallons per mile, use fuel / distance instead.

[RacingLists]

Thanks all!

Yes, the dates are irrelevant since the odometer reading is successional and will be sorted lowest to highest for each bike (not that I knew that in the beginning but I did not understand that the first odometer reading would be taken into the calculation automatically, which makes sense). Precision on the odometer data is fairly good since we almost know how many km they travelled (a small amount is not accounted for from the start to the first fillup). So, there is a glitch in the existing data, I could estimate it, but the volume in question is small (<.1% of the total) so we'll ignore it in this dataset. But this year, that will need to change.

So Litres/100km =

Let ( [

distance = Last ( Entries::Odometer ) - Entries::Odometer ;

fuel = Sum ( Entries::FuelIntake ) - Entries::FuelIntake

] ;

( fuel / distance ) *100

)

In the first solution offered by swf, there was a clone of the table created. This is what I don't understand. Why was that necessary (not doubting for your solution, just don't understand) or can you provide a key word so I can look it up please?

In your example Comment, there is no indication that the clone table is needed. Is that correct? Laretta referred to this as the, 'isolate'?

Edited April 24, 2006 by Guest

[comment]

I think by "clone table" you mean a self-join relationship??

I think you need 2 separate tables (at least) Bikes and Entries (or PitStops, Fuellings, whatever). The relationship should be:

Bikes::BikeID = Entries::BikeID

The above calculation should be in the Bikes table, so you get one result for each bike.

[LaRetta]

Hi racinglists,

If you apply Comment's calculation to the existing table, you will get the totals and averages of ALL bikes together. The reason I added the self-join BikeNumber = BikeNumber was to produce figures per bike. I believe that Michael was referring (more correctly) to the self-join by the descriptive name ENTRIES rather than BikeRace2 (as I sloppily named it). But yes, it needs the self-join to isolate by bike. Self-joins are also called clones but they are used to restrict relationships - just like attaching to any other table occurrence restricts to only related.

Michael, thanks for the clarification. I've never worked with fuel/mileage and I couldn't grab the logic of it - something seemed to be missing in my vision of the fuel. But I figured it was a guy-thing.

LaRetta

[comment]

But I figured it was a guy-thing.

Why sure, take the easy way out - blame it on glands...

---

While I'm here again: the parenthesses in the final calc are not required.

fuel / distance * 100

will return the same result.

[RacingLists]

I've been fiddling, but I can't get browse data to show the bikenumber and fuel efficiency for each bike. What I get is the correct data, but for each record of each bike.

LaRetta, as you indicated, the self join was created to facilitate showing the calcs for separate bikes, not each occurence of 'entries' for each bike. How do I get it to show only one entry with the calculation without a summary table and preview mode (which I can do)? Are the two tables 'clones' with the fuel efficiency calculation in 'bikes', whereas 'entries' is the self join or clone as Comment indicated? I don't get it quite yet...also, I destroyed my working copy by deleting one of the TOs so need to start again but thought I'd ask before trying to structure this...

Thanks!

Edited April 24, 2006 by Guest

[LaRetta]

My file showed it pretty clearly, I thought? If you don't have two tables (or files) with Bikes and Entries (as Comment suggested, which is the best way) ... if you are using one file (holding multiple bikes with multiple entries) as you originally indicated, then you need a self-join to isolate the bike totals. If structured correctly, Comment's calc is much better - but you need to use the self-join fields and NOT fields from your original table.

Edited April 24, 2006 by Guest

[RacingLists]

so comment's instructions to have the fuel efficiency calc in, 'bikes' and not 'entries' means that bikes is the self join? I'll go back to your original file...

[comment]

Try it this way? Of course, that's just a basic start. You might want to add a Races table, and a bunch of other stuff.

---

EDIT:

My "instructions" were to replace the self-join with a proper relational structure.

MotorRace.fp7.zip

Edited April 24, 2006 by Guest

[RacingLists]

so going back to your original example, the calculation is in BikeRace while the calc refers to the data in BikeRace2. I need 'relationships 101'!

[RacingLists]

Okay, I understand now. Forget Relationships 101, I need grade 8 databases.

[RacingLists]

Another question Comment...The solution works when entering data, but now how would I import data into your solution? Do I import into PitStops? If so, 'bikenumber' from my data goes into what field?

Thanks!

Edited April 24, 2006 by Guest

[comment]

If you mean the odometer/fuelling data, then yes. The bikenumber could go into the BikeID field, or you could add a field for it. But you need to have a matching record in the Vehicles table, and the match field has to be unique there.

[RacingLists]

Thanks for that! It's finished and works like a charm and just in time for tonight's meeting. I'll be singing the praises of a guy named, 'comment' tonight who bailed my ass out of a sling to get these guys their data (new rules this year on limiting tank size which will make for a lot of unhappy folks as their engines are so built up, they can't go very far on one small tank of gas!).