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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]type[/TD]
[TD]date[/TD]
[TD] expected result stop date[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]s[/TD]
[TD]1/1/2019[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]c[/TD]
[TD]1/12/2019[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]c[/TD]
[TD]1/23/2019[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]e[/TD]
[TD]2/15/2019[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]e[/TD]
[TD]2/17/2019[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]s[/TD]
[TD]1/26/2019[/TD]
[TD]1/31/2019[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]e[/TD]
[TD]1/31/2019[/TD]
[TD]1/31/2019[/TD]
[/TR]
</tbody>[/TABLE]



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