Multi row calculation help

[Rag1]

Here is my data

ID	RANKING	DOMAIN
X	1	support
X	2	agent request
Y	1	unknown
Y	2	unknown
Y	3	agent request
Y	4	support
Z	1	unknown
Z	2	unknown

I am trying to get first ranked domain for each ID.( for ID 'X ' the DOMAIN value should be 'support') 

And if there is 'unknown' then give me the first unknown value in the sequence.(for ID 'Y' the DOMAIN value should be 'agent request')

And if all the Domain values are unknown then give me unknown(for ID 'Z' the Domain value should be 'unknown')

The final table should look like this

ID	NEW DOMAIN
X	support
Y	agent request
Z	unknown

Thanks a lot for looking into this, hopefully someone can guide me.

[Luke_C · Accepted solution]

hi @Rag1 

Here's how I'd approach:

1. Sort, filter out unknowns, and sample the first value for each ID. This handles the first two conditions you listed.
2. Then, compare the IDs we sampled to the overall list of IDs. For any that don't match up make those values unknown

[Rag1]

Thanks Luke, it worked perfectly!

[flying008]

Hi, @Rag1 

FYI.

Spoiler

IF [DOMAIN] = Trim(Replace(PadRight('', [RANKING], '|'), '|', 'unknown,'), ',') 
THEN 'unknown' 
ELSEIF StartsWith([DOMAIN], 'unknown') 
THEN Left(Replace([DOMAIN], 'unknown,', ''), FindString(Replace([DOMAIN], 'unknown,', ''), ','))
ELSE Left([DOMAIN], FindString([DOMAIN], ','))
ENDIF