In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Alteryx formula

SouravKayal
8 - Asteroid

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

5 REPLIES 5
BS_THE_ANALYST
14 - Magnetar
14 - Magnetar

@SouravKayal I think this is doing what you want! The unique tool can help with taking the first instance of something appearing in a list:

BS_THE_ANALYST_0-1676539261496.png

 

 

All the best,
BS

LinkedIN

Bulien
SouravKayal
8 - Asteroid

But unique will give me the forst occurence i want to sort first red then amber then green

BS_THE_ANALYST
14 - Magnetar
14 - Magnetar

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

 

All the best,
BS

LinkedIN

Bulien
SouravKayal
8 - Asteroid

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

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

DataNath
17 - Castor
17 - Castor

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

 

DataNath_0-1676540288741.png

 

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

Labels
Top Solution Authors