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")
Solved! Go to Solution.
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
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
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
@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.
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.
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.