Distance and MPG

user07657

Hello,

 

i have a dataset that has consists of Vehicle gas fill up data.

 

date of gas fill up

odometer reading (miles) at time of fill up

gallons pumped

cost per gallon

total spent

vehicle

 

I'd like to have a card represent by vehicle the calculated MPG, and the distance traveled.

 

This would be by month/quarter/year depending on the cards time selection.

 

Is this possible?

 

Thanks! 

amnel

Hello,

 

Assuming you have all the necessary data pieces - which it looks like you do -  this should be no problem. 

 

The catch is if the data is in the correct structure to do this in a Beast Mode or if you have to transform the data a bit via dataflow first.

 

Could you provide a sample of the data?

 

 

user07657

Please see the attached with sample data

RGranada

Hi,

 

I would make a transformation in a redshift dataflow in order to get the odometer value in the last reffil.

Like this :

 

Select *,NVL(LAG("odometer mileage") RESPECT NULLS OVER (PARTITION BY vehicle ORDER BY date),0) PreviousOdometer from tst_dojo_gas 

 

Then you can in a BeastMode get the Distance Traveled :

 

`odometer mileage`-`previousodometer`

 

And from here get the MPG also :

 

`gallons pumped`/(`odometer mileage`-`previousodometer`)

 

Sending as an attachement the export of the result table

 

Hope it helps.

 

 

user07657

Ricardo,

 

Thank you for your reply!  It certainly got the wheels turning and i appreciate the assist.

 

Would this be something possible to perform in Magic ETL?

 

Also, does this need to be done in dataflow or any possibilities within beastmode/card design?

 

Thanks!

RGranada

Hi.

 

I don't know if window functions can be used in Magic ETL. And i can't see any way for a bestmode to get previous record values...

 

But we never know!