topic Re: Variance Between Table Data - Load in QlikView

Variance Between Table Data - Load

goro2010 — Tue, 12 May 2015 13:42:15 GMT

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

rubenmarin — Tue, 12 May 2015 13:50:48 GMT

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;

Re: Variance Between Table Data - Load

goro2010 — Tue, 12 May 2015 13:58:00 GMT

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

goro2010 — Tue, 12 May 2015 14:10:27 GMT

Ruben,

Like I said, blond moment!!

Your suggestion worked 1000%.

Thank You!!!

Re: Variance Between Table Data - Load

rubenmarin — Tue, 12 May 2015 14:10:55 GMT

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

Re: Variance Between Table Data - Load

rubenmarin — Tue, 12 May 2015 14:11:43 GMT

Nice, glad to help you.

Re: Variance Between Table Data - Load

goro2010 — Tue, 12 May 2015 14:18:18 GMT

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

goro2010 — Tue, 12 May 2015 14:22:32 GMT

Got it right,

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

Thank You!!

Re: Variance Between Table Data - Load

rubenmarin — Tue, 12 May 2015 14:22:40 GMT

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