topic Re: Return the next n row with aggregation in QlikView

Return the next n row with aggregation

Thu, 23 Feb 2017 02:07:36 GMT

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!

Re: Return the next n row with aggregation

sunny_talwar — Thu, 23 Feb 2017 10:25:25 GMT

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?

Re: Return the next n row with aggregation

Fri, 24 Feb 2017 03:24:42 GMT

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.

Re: Return the next n row with aggregation

sunny_talwar — Sun, 26 Feb 2017 17:16:37 GMT

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)

Re: Return the next n row with aggregation

Fri, 03 Mar 2017 02:07:11 GMT

It works! Many thanks indeed, Sunny.