IF function for multiple columns and critieria

[SiddeshDhruva]

Hello everyone

i am trying to write a logic in alteryx to compare the data between multiple columns and group based on the data input. below is the formula which is written in excel however i want to replicate the same in alteryx as well, can someone please guide me here! i have also attached the dummy file FYR

IF(AND(OR(total revenue<>""),OR(Company="Recognized")),"Client",IF(AND(OR(total revenue<>""),OR(Company<>"Recognized")),"Unrecognized Client",IF(AND(OR(total revenue<=0),OR(Company="Recognized"),OR(Owner<>"")),"Prospect", IF(AND(OR(total revenue<=0),OR(Company=<>"Recognized"),OR(Owner="")),"Uncovered","None"))))

[PHinkel]

Hi @SiddeshDhruva , take a look at the formula below or the attached solution and let me know what you think:

IF [Total Revenue] != Null() AND [Company] = "Recognized" THEN "Client"
ELSEIF [Total Revenue] != Null() THEN "Unrecognized Client"
ELSEIF [Banker] != Null() THEN "Prospect"
ELSEIF [Company] != "Recognized" THEN "Uncovered"
ELSE "None"
ENDIF

[SPetrie · Accepted solution]

Your excel formula is a bit overcomplicated as well, so that may be increasing any difficulty you are having.

You can condense it to be this and end up with the same result.

=IF(AND(D2<>"",B2="Recognized"),"Client",IF(AND(D2<>"",B2<>"Recognized"),"Unrecognized Client",IF(AND(D2<=0,B2="Recognized",C2<>""),"Prospect", IF(AND(D2<=0,B2<>"Recognized",C2=""),"Uncovered","None"))))

To translate that into Alteryx using the headers in your dummy file, it would look like this

if
!IsNull([Total Revenue]) and [Company]="Recognized" then "Client"
elseif
!IsNull([Total Revenue]) and [Company] != "Recognized" then "Unrecognized Client"
elseif
([Total Revenue]<=0 or isnull([Total Revenue])) and [Company] = "Recognized" and !IsNull([Banker]) then "Prospect"
elseif
([Total Revenue] <=0 or isnull([Total Revenue])) and [Company] !="Recognized" and IsNull([Banker]) then "Uncovered"
else "None" endif