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

Question

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.

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

JohnJPS · Accepted Answer

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 · Accepted Answer

@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!