2 Replies Latest reply on Jan 18, 2019 3:17 AM by jolt

    Return value of a column from the row after the row where some condition applies

    jolt

      Hey guys,

       

      I'm stuck with calculating the following:

       

      For each id_foreign_x give me the value of the column date_inserted of the row after the row where MAX(date_inserted) AND action = 'close'

       

      What I came up with is

      {FIXED [Id Claim]:MAX(if [Action]='close' then [Date Inserted] end)}

      which gives me the  Max(date_inserted) of the action = 'close'. eg. for id_foreign_x = 7 it returns 2018-01-08 17:19:59.

      But I'm interested in the date_inserted of the next row of that id_foreign_x...

       

       

      Table looks like this:

      row numberid_action
      id_foreign_xactiondate_inserted
      1207xyz2018-01-03 14:44:57
      22112close2018-01-03 15:40:01
      3225write_message2018-01-03 20:33:45
      4239write_message2018-01-04 10:20:38
      5247change_group2018-01-05 12:17:18
      6257close2018-01-08 17:19:59
      7267change_status2018-01-10 11:05:23
      82712write_message2018-01-11 09:15:44
      9287write_message2018-01-12 14:01:05

       

      So what I need is

      id_foreign_x = 7 --> calculated value = 2018-01-10 11:05:23

      id_foreign_x = 12 --> calculated value = 2018-01-11 09:15:44

       

      I hope I could explain the problem and the desired outcome clear enough.

      Looking forward to some input