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 IFS(AND(Preferred Suppliers="",Banker="",Revenue total =500000,2022 Spend>=500000)),"Unprocessed",(AND(Preferred Suppliers="",Banker="",Revenue total<>0)),"Unassigned Client",(AND(Preferred Suppliers="",OR($Banker<>""),Revenue total<>0,OR(2021 Spend>500000,2022 Spend>500000))),"Client",(AND(Preferred Suppliers="",OR(Banker<>""),Revenue total =500000,2022 Spend>=500000))),"Pipeline", Preferred Suppliers="Yes", "Preferred") Example.xlsx

IF function for multiple columns and criteria

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

IFS(AND(Preferred Suppliers="",Banker="",Revenue total<=0,OR(2021 Spend>=500000,2022 Spend>=500000)),"Unprocessed",(AND(Preferred Suppliers="",Banker="",Revenue total<>0)),"Unassigned Client",(AND(Preferred Suppliers="",OR($Banker<>""),Revenue total<>0,OR(2021 Spend>500000,2022 Spend>500000))),"Client",(AND(Preferred Suppliers="",OR(Banker<>""),Revenue total<=0,OR(2021 Spend>=500000,2022 Spend>=500000))),"Pipeline", Preferred Suppliers="Yes", "Preferred")

Example.xlsx

Developer

Designer Cloud

Common Use Cases

Best Practices

Accepted answers

Qiu

@SiddeshDhruva
Allow me to jump in and it is brain burning... That is why we like Alteryx over Excel...😂

We need to do cleansing of the data to replace null with 0 for numeric filelds otherwise the operators (=, <, > ) will not work.

Then we better organize the conditional statment in a hierachy structure.

0413-SiddeshDhruva.yxzp

All comments

Watermark

Give me a couple min to write it out, but you'd essentially do the same thing.

You'd use a formula tool, formatted

"IF" x THEN y

Else if z the ab

Elseif n then cd

Elseif M the ef

Else "none of the above"

Endif

Watermark

So breaking open your long statement into a digestible format it goes:

IFS

(AND(Preferred Suppliers="",Banker="",Revenue total<=0,OR(2021 Spend>=500000,2022 Spend>=500000)),"Unprocessed",

(AND(Preferred Suppliers="",Banker="",Revenue total<>0)),"Unassigned Client",

(AND(Preferred Suppliers="",OR($Banker<>""),Revenue total<>0,OR(2021 Spend>500000,2022 Spend>500000))),"Client",

(AND(Preferred Suppliers="",OR(Banker<>""),Revenue total<=0,OR(2021 Spend>=500000,2022 Spend>=500000))),"Pipeline",

Preferred Suppliers="Yes", "Preferred")

So set up your IF statement in your formula for 4 different If : Then pieces, and your final one would be the else statement.
Your first line would look like this:

IF [Preferred Suppliers]="" AND [Banker] ="" AND [Revenue total]<=0 AND ([2021 Spend]>=500000 or [2022 Spend]>=500000)

THEN "Unprocessed"

Model the other 3 the same way then finish with

Else "prefered"

ENDIF

Watermark

btw - If your final one won't always result in "prefered" then you could add another statement Elseif [preferred suppliers]='yes' then 'prefered'

and use a final

Else ....... <whatever happens if none of the criteria are met>

Endif

Qiu

@SiddeshDhruva
Allow me to jump in and it is brain burning... That is why we like Alteryx over Excel...😂

We need to do cleansing of the data to replace null with 0 for numeric filelds otherwise the operators (=, <, > ) will not work.

Then we better organize the conditional statment in a hierachy structure.

0413-SiddeshDhruva.yxzp

Yoshiro_Fujimori

Hi @SiddeshDhruva ,

As @Watermark mentioned, if the conditions are clearly defined, "IF_THEN_ELSE" statement should do all the work.

However, when putting it into practice, it might be hard to understand and maintain the logic.

If you think the same way, it may be better to visualize the conditions using Filter tool as below.

Ideally, each output from all the Filter tools should have some defined value, but if that is not the case, you may just add the output to Union Tool.

I believe you should be able to simplify the conditions further, which will also simplify the workflow.

Good luck.

If_Functions.yxmd

Watermark

As the reply from both @Yoshiro_Fujimori and @Qiu mention and I left unsaid, you do have to be aware of the quality of the data. I left it at simply following along with what your formula states. I kind of addressed data after the fact in my second note because it wasn't spelled out. What to do if there is a set of values that fall outside of conditions listed by your formula? Regardless if they're a data quality issue (nulls & zeros), or merely new conditions not covered by the current formula, how is that handled? Data quality & consistency, clean up, new circumstances, all these things can be built in, and both the other posts rightly bring them up for consideration.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703