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

IF function for multiple columns and criteria

SiddeshDhruva
7 - Meteor

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")

 

 

6 REPLIES 6
Watermark
12 - Quasar
12 - Quasar

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
12 - Quasar
12 - Quasar

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
12 - Quasar
12 - Quasar

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
20 - Arcturus
20 - Arcturus

@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.PNG0413-SiddeshDhruva-A.PNG0413-SiddeshDhruva-B.PNG

Yoshiro_Fujimori
15 - Aurora

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.

Yoshiro_Fujimori_1-1681370202577.png

 

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.

Watermark
12 - Quasar
12 - Quasar

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.

Labels