Lookupvalue with Filters and Conditions

[Joe_adb]

Hi,

I need help to fetch the results displayed in Table2 from Table1. Please suggest a query that matches the below conditions and update the data in Value_ column in Table2

Conditions:

• The value against the ID should be captured only if CC stage comes after PD:SD. IF CC is before PD:SD then that value should be ignored
• CC stage without PD:SD should be ignored
• First CC stage after PD:SD should be captured

Table1				Table 2
ID	Stage	Value		ID	Value_
11206290	SD	1		11206290
11206290	PD:SD	2		20113299	4
11206290	SD	3		20063390	5
20113299	SD	1		20085555
20113299	PD:SD	2		20229876	6
20113299	SD	3
20113299	CC	4
20063390	CC	1
20063390	PD:SD	2
20063390	SD	3
20063390	SD	4
20063390	CC	5
20085555	SD	1
20085555	CC	2
20229876	SD	1
20229876	PD:SD	2
20229876	SD	3
20229876	PD:SD	4
20229876	SD	5
20229876	CC	6
20229876	CC	7

[Anonymous]

Hi @Joe_adb 

I have a doubt , how did you judge the value for “20063390” and “20229876” ? From the data you provided, the values are not in order . In this case, what is the logic to get the value ?

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_adb]

Hi Alisa,

Thanks for looking into this query. The "Value" column in Table1 is actually the ID group index number. Value for “20063390” and “20229876” was returned as 5 & 6 as it matches the below condition mentioned in the question.

• The value against the ID should be captured only if CC stage comes after PD:SD. IF CC is before PD:SD then that value should be ignored
• First CC stage after PD:SD should be captured