Variance Between Table Data - Load

[goro2010]

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

[rubenmarin · Accepted solution]

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:

LOAD ....,

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

     ....

FROM....

Order By Pin, created_at;

[goro2010]

Hi Ruben,

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

[goro2010]

Ruben,

Like I said, blond moment!!

Your suggestion worked 1000%.

Thank You!!!

[rubenmarin]

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

[rubenmarin]

Nice, glad to help you.

[goro2010]

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

[goro2010]

Got it right,

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

Thank You!!

[rubenmarin]

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],