Multiple IF statements

[ahsansalik]

Hi I have the following data

Department	Component	Action	Root Cause	Reason	Further Investigation
Electrical	Battery	Replaced	Not Rechargeable	Fail	Tv Remote
Electrical	Battery	Recharged	Excessive Use	Fail	Mobile Phone
Electrical	Battery	Recharged	Excessive Use	Fail	Game Controller
Electrical	Battery	Replaced	Not Rechargeable	Fail	Toy

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

[apathetichell · Accepted solution]

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 · Accepted solution]

Hi @ahsansalik 

Attached is a workflow to reference 

[ahsansalik]

Hi Carlie

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

[CarliE]

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

[apathetichell]

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]

@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]

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...