Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
goro2010
Contributor

Variance Between Table Data - Load

Good Day,

I hope you can assist me.

I have the data below:

img.png

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

1 Solution

Accepted Solutions

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:

LOAD ....,

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

     ....

FROM....

Order By Pin, created_at;

8 Replies

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:

LOAD ....,

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

     ....

FROM....

Order By Pin, created_at;

goro2010
Contributor

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:

img2.png

goro2010
Contributor

Re: Variance Between Table Data - Load

Ruben,

Like I said, blond moment!!

Your suggestion worked 1000%.

Thank You!!!

Re: Variance Between Table Data - Load

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

Re: Variance Between Table Data - Load

Nice, glad to help you.

goro2010
Contributor

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

goro2010
Contributor

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

Community Browser