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


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