Return the next n row with aggregation

[#1]

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 ID	City	Arrival Date	Arrival Time
BUS01	Barcelona	20170223	0910
BUS01	Valencia	20170223	1500
BUS01	Madrid	20170224	0820
BUS01	Salamanca	20170224	1015
BUS02	San Francisco	20170223	0800
BUS02	Oakland	20170223	0830
BUS02	Berkeley	null	null
BUS02	Richmond	20170224	0815

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!

[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?

[#3]

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 · Accepted solution]

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)

[#5]

It works! Many thanks indeed, Sunny.