lookupvalue same table

[frankee_gee]

Help would be appreciated

trying to use lookupvalue to return an "end" date which is determined by table[type]

table[ID]
-will be duplicated for each table[type]
-each table[type] can be duplicated for each table[ID]

table[date]


table[type]
-"s" - start
-"c" - continue
-"g" - going
-"e" - end

id	type	date	expected result stop date
22	s	1/1/2019	2/17/2019
22	c	1/12/2019	2/17/2019
22	c	1/23/2019	2/17/2019
22	e	2/15/2019	2/17/2019
22	e	2/17/2019	2/17/2019
88	s	1/26/2019	1/31/2019
88	e	1/31/2019	1/31/2019

<tbody>
</tbody>

thank you in advance.

 

[peter789]

Load the table to PQ.
Duplicate it.
In the copy filter by type ="e".
Group by id return max of date.
Merge this query back with original joined on id; expand on the max date.

Peter