Forum Discussion

Joe_adb's avatar
Joe_adb
Regular Visitor
4 years ago

Lookupvalue with Filters and Conditions

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 
IDStageValue IDValue_
11206290SD1 11206290 
11206290PD:SD2 201132994
11206290SD3 200633905
20113299SD1 20085555 
20113299PD:SD2 202298766
20113299SD3   
20113299CC4   
20063390CC1   
20063390PD:SD2   
20063390SD3   
20063390SD4   
20063390CC5   
20085555SD1   
20085555CC2   
20229876SD1   
20229876PD:SD2   
20229876SD3   
20229876PD:SD4   
20229876SD5   
20229876CC6   
20229876CC7   

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Joe_adb
      Regular Visitor
      4 years ago

      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