8 Replies Latest reply: May 12, 2015 10:22 AM by Ruben Marin

# Variance Between Table Data - Load

Good Day,

I hope you can assist me.

I have the data below:

I am currently using the below in my loading script but it is giving me an issue:

if(Previous(pin)=pin, Interval(created_at-Previous(created_at),'hh:mm:ss')) as [Travel Time]

I am looking for the variance between data using the "pin" as my reference, the above code works if the data is following each other but not when there is other data in between, as per the example attached, on row 2 the code worked as the same pin was in the previous line, but in row 4 nothing was populated as the pin is different in row 3.

How can I resolve this that the script must look for the previous "pin" = "pin" and ignore the other in between, I will require this for Traveling Time and Distance Traveled?

Any help would be appreciated.

Thank You

• ###### Re: Variance Between Table Data - Load

Hi Jan, for use of Peek() and Previous() functions usually you need an 'Order By' clause, so the records are sorted the way you need, in your case can be something like:

if(Previous(pin)=pin, Interval(created_at-Previous(created_at),'hh:mm:ss')) as [Travel Time],

....

FROM....

Order By Pin, created_at;

• ###### Re: Variance Between Table Data - Load

Hi Ruben,

Thank You for the reply, I am thinking that I have an blond moment but now I get this message below:

• ###### Re: Variance Between Table Data - Load

Ruben,

Like I said, blond moment!!

Thank You!!!

• ###### Re: Variance Between Table Data - Load

Hi Jan, the "Order By" should go after the "(qvd)".

• ###### Re: Variance Between Table Data - Load

Hi Ruben,

Ok, the time is sorted

How will the difference between a sum be like, this is giving me an error as per below:

if(Previous(pin)=pin, Sum(odo - Previous(odo))) as [Traveled KMs],

Thank You

• ###### Re: Variance Between Table Data - Load

Got it right,

if(Previous(pin)=pin, RangeSum(odo)-Previous(odo)) as [Traveled KMs],

Thank You!!

• ###### Re: Variance Between Table Data - Load

Hi Jan, what are you trying to do?

In that row you don't need a Sum, Previous(odo) will return only one value, so that is the same than:

if(Previous(pin)=pin, odo - Previous(odo)) as [Traveled KMs],