Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return the next n row with aggregation

Hi, I am creating an analytic for vehicle tracking. I would like to return the next stop(s) when a vehicle and a stop is selected

Vehicle IDCityArrival DateArrival Time
BUS01Barcelona201702230910
BUS01Valencia201702231500
BUS01Madrid201702240820
BUS01Salamanca201702241015
BUS02San Francisco201702230800
BUS02Oakland201702230830
BUS02Berkeleynullnull
BUS02Richmond201702240815

When 'BUS01' and 'Valencia' is selected, it should return BUS01's next stop 'Madrid' and 'Salamanca' as the next 2 stop

It should skip the null arrival date row as the bus did not stop by the city as planned. E.g. next stop of 'BUS02', 'Oakland' should be 'Richmond' instead of 'Berkeley'.

I have been trying with the combination of below() and aggr() but not really returning what I've been looking for.

Many thanks in advance!

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD [Vehicle ID],

    City,

    Date(Date#([Arrival Date], 'YYYYMMDD')) as [Arrival Date],

    Time(Time#([Arrival Time], 'hhmm')) as [Arrival Time],

    TimeStamp(Date#([Arrival Date], 'YYYYMMDD')+Time#([Arrival Time], 'hhmm')) as [Arrival Timestamp]

FROM

[https://community.qlik.com/thread/250985]

(html, codepage is 1252, embedded labels, table is @1);

Expressions

=FirstSortedValue({<City, [Arrival Timestamp] = {"$(='>' & TimeStamp(Min([Arrival Timestamp])))"}>}City, [Arrival Timestamp])

=FirstSortedValue({<City, [Arrival Timestamp] = {"$(='>' & TimeStamp(Min([Arrival Timestamp])))"}>}City, [Arrival Timestamp], 2)

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

When you say return, return how? In a table? text box object? do you have a sample where you might have been trying this out?

Not applicable
Author

Hi Sunny. Let say a text object. I am afraid that I am not able to provide a sample qvw. An expression based on the above sample would be very helpful. Cheers.

sunny_talwar

Try this:

Table:

LOAD [Vehicle ID],

    City,

    Date(Date#([Arrival Date], 'YYYYMMDD')) as [Arrival Date],

    Time(Time#([Arrival Time], 'hhmm')) as [Arrival Time],

    TimeStamp(Date#([Arrival Date], 'YYYYMMDD')+Time#([Arrival Time], 'hhmm')) as [Arrival Timestamp]

FROM

[https://community.qlik.com/thread/250985]

(html, codepage is 1252, embedded labels, table is @1);

Expressions

=FirstSortedValue({<City, [Arrival Timestamp] = {"$(='>' & TimeStamp(Min([Arrival Timestamp])))"}>}City, [Arrival Timestamp])

=FirstSortedValue({<City, [Arrival Timestamp] = {"$(='>' & TimeStamp(Min([Arrival Timestamp])))"}>}City, [Arrival Timestamp], 2)

Capture.PNG

Not applicable
Author

It works! Many thanks indeed, Sunny.