Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple IF statements

ahsansalik
8 - Asteroid

Hi I have the following data

 

Department ComponentActionRoot CauseReasonFurther Investigation
ElectricalBatteryReplacedNot RechargeableFailTv Remote
ElectricalBatteryRechargedExcessive UseFailMobile Phone
ElectricalBatteryRechargedExcessive UseFailGame Controller
ElectricalBatteryReplacedNot RechargeableFailToy

 

I have written a code to change Reason and Device based on the Root Cause and Department

 

IF [Department] ='Electrical' and [Root Cause] = 'Not Rechargeable' OR [Root Cause]='Excessive Use' Then [Reason]= ' Battery Fail' 
else "Other" endif

 

IF [Department] ='Electrical' and [Root Cause] = 'Not Rechargeable' OR [Root Cause]='Excessive Use' Then [Further Investigation]= 'Battery Drained' 
else "Other" endif

 

 

 

Basically on the basis of the department Root cause combination i want to change the current values in Reason and Device
but its not doing what i need it to do

 

also i am getting confused with what quotes " and '  are used for "Battery" or 'Battery' what is the difference

7 REPLIES 7
apathetichell
19 - Altair

These are two distinct formulas and need to be separated in the formula tool (ie click on the plus sign to open a second formula)... First has to create a new variable called Reason and it should read:

IF [Department] ='Electrical' and [Root Cause] = 'Not Rechargeable' OR [Root Cause]='Excessive Use' Then ' Battery Fail'
else "Other" endif

 

second variable is called further investigation and the formula is:

IF [Department] ='Electrical' and [Root Cause] = 'Not Rechargeable' OR [Root Cause]='Excessive Use' Then 'Battery Drained'
else "Other" endif

 

' and " are basically the same and only differ when you have an outer '" and you need to nest something...

CarliE
Alteryx Alumni (Retired)

Hi @ahsansalik 

 

Attached is a workflow to reference 

Carli
ahsansalik
8 - Asteroid

Hi Carlie

how many OR can i add is there a limit in Alteryx

CarliE
Alteryx Alumni (Retired)

There is no limit. But, I would use some parenthesis to make sure the correct criteria are grouped together.

Carli
apathetichell
19 - Altair

At a certain point in "or"s it's probably easier to use a join tool with another file and then filter out the records that did and didn't join...

 

so like let's say you are checking if a value has the number 1-Nin it (obviously with math functions this is easier but suspend your disbelief for the duration of this illustration)... you can use if [value] =1 or ... [value]=n...

 

but another way is to have a separate data stream with values 1-n in a field. you join the two. The resulting join stream are entities where your original datastream had values 1-n in the join field... much easier than lots of "or"s

patrick_digan
17 - Castor
17 - Castor

@ahsansalik The only other thing I would mention is the IN function which can simplify your formula:

IF [Department ] ='Electrical' and [Root Cause] IN ('Not Rechargeable','Excessive Use') Then 'Battery Drained' 
else "Other" endif

 

apathetichell
19 - Altair

In the unlikely event you are curious what @CarliE 's "no limit" really means -  I just got Summarize (5) Error String size limit reached: Strings are limited to 2147483648 bytes somewhere around 28,000,000 ors... 1,000,000 ran without a major problem... note: this was fed into a dynamic replace tool...

Labels
Top Solution Authors