Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Compare Several Columns and return the Highest Value in a New Column

BeckyS
6 - Meteoroid

I am trying to return the highest numerical value of five columns (E/M Code1-5) in a new column named Highest E/M Code. I tried using an IF statement which only returned empty cells in the new column.

 

 

EM Columns.PNG

This is the IF statement I used:

 

IF
[E/M Code1] > [E/M Code2] && [E/M Code3] && [E/M Code4] && [E/M Code5]
Then
[E/M Code1]
Else
""
Endif
+
IF
[E/M Code2] > [E/M Code1] && [E/M Code3] && [E/M Code4] && [E/M Code5]
Then
[E/M Code2]
Else
""
Endif
+

IF
[E/M Code3] > [E/M Code2] && [E/M Code1] && [E/M Code4] && [E/M Code5]
Then
[E/M Code3]
Else
""
Endif
+
IF
[E/M Code4] > [E/M Code2] && [E/M Code3] && [E/M Code1] && [E/M Code5]
Then
[E/M Code4]
Else
""
Endif
+
IF
[E/M Code5] > [E/M Code2] && [E/M Code3] && [E/M Code4] && [E/M Code1]
Then
[E/M Code5]
Else
""
Endif

 

3 REPLIES 3
JohnJPS
15 - Aurora

Hi @BeckyS

 

You're in luck, Alteryx has a function "Max" for just this purpose:

 

Max([E/M Code1],[E/M Code2],[E/M Code3],[E/M Code4],[E/M Code5])

 

patrick_digan
17 - Castor
17 - Castor

@BeckyS I think you could use a max function like:

Max([E/M Code1],[E/M Code2],[E/M Code3],[E/M Code4],[E/M Code5])

For your method, you would need to slightly tweak it to: 

IF
[E/M Code1] > [E/M Code2] && [E/M Code1] >  [E/M Code3] && [E/M Code1] > [E/M Code4] && [E/M Code1] > [E/M Code5]
Then
[E/M Code1]
Else
""
Endif

Each time you check the greater than, you would need to repeat your initial variable. The max function should be able to do the trick instead!

BeckyS
6 - Meteoroid

That was simple. Thanks guys!

Labels