4 Replies Latest reply: Mar 2, 2017 9:07 PM by Kevin Lee RSS

    Return the next n row with aggregation

    Kevin Lee

      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!