How to chooose one value based on applying conditions to table column

[Naga]

I have a table like below. Which plots different services under one column Service A (Subservices - A1 to A5) / Service B (Subservices - B1 to B5) .  I need to take a new column denotes one Final Status like this if any of one Status is RED then the final status is RED, If there is no RED but one YELLOW And many GREEN then final status if YELLOW. What will be the best condition i can use to achieve the final one result

Service	Status
A1	GREEN
A2	RED
A3	YELLOW
A4	GREEN
A5	GREEN

[renjith_nair]

Try

|eventstats values(Status) as StatusList by Parent
|eval FinalStatus=case(isnotnull(mvfind(StatusList,"RED")),"RED",isnotnull(mvfind(StatusList,"YELLOW")),"YELLOW",isnotnull(mvfind(StatusList,"GREEN")),"GREEN",1==1,"NA")
|fields - StatusList

Here Parent is 'A'

Run anywhere example

|makeresults|eval Service="A1 A2 A3 A4 A5"|makemv Service|mvexpand Service
|appendcols [|makeresults | eval Status="GREEN RED YELLOW GREEN GREEN"|makemv Status|mvexpand Status]
|table Service, Status
|rex field=Service "(?<Parent>\D+)"
|eventstats values(Status) as StatusList by Parent
|eval FinalStatus=case(isnotnull(mvfind(StatusList,"RED")),"RED",isnotnull(mvfind(StatusList,"YELLOW")),"YELLOW",isnotnull(mvfind(StatusList,"GREEN")),"GREEN",1==1,"NA")
|fields - StatusList

Parent extraction is a simple rex for this dummy data and you should change based on actual data

You can replace eventstats with stats if you want only one status per service