Alteryx formula

Question

Hi ,

i am trying to achieve the below but its getting too complicated.

i have like a few ids and rag associated with them but i need to chose the highest based on hierarchy.

ex

id. Rag

1.    Red

2.    Amber

3.    Green

3.     Red

3.   Red

1. amber

2 green

so what i need is 1 to show red 2 to show amber and 3 to show red

so if red is there it will be red. If amber then amber if only green then green.

i was trying to do a summarise and then take count and then apply a formula but i think there could be an easier way to do this. Can someone please guide

DataNath · Answer

Hey @SouravKayal, here's one way you could go about this:

The workflow:

1) Assigns a rank to the [Rag] value - Red = 1, Amber = 2, Green = 3

2) The 2 x Summarize tools a) pull out the [Min_Rank] for each ID and, b) create a form of lookup for the rank in a single row each so we don't explode the data

3) Join the [Min_Rank] to the corresponding [Rag]

4) Sort the IDs into the original order

Rag assignments.yxmd

SouravKayal · Answer

As I mentioned so input data could be

Id rag

1 red

1 amber

1 green

1 amber

2 red

2 green

3 amber

3 green

4 green

5 red

5 green

output expected

1 red

2 red

3 amber

4 green

5 red

so where ever there is red i want red for that id , where there is amber and green i want amber and only green then green

BS_THE_ANALYST · Answer

@SouravKayal could you provide the Sample Input and Expected Output. This way I can build a logic