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

• ###### 1. Re: Return value of a column from the row after the row where some condition applies

Hi Jolt

Please follow the Steps in attached. Calculation 6 is Combined Calc which I used to get There.Calculation 5 will also return same values.

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: Return value of a column from the row after the row where some condition applies

Thank you very much Deepak! This is looking great.

Would it also be possible to get this result if the table wouldn't have the column 'row number'? I couldn't modify your great calculation in a way that worked.