How to use multiple IF statements with multiple AND statements

If someone could help me with designing a multiple if statement with 3 conditions, I would appreciate your help!!

I have two categories that I would like to draw from: age and gender.

I would like to say, if the age is from 10 to 13 and is a female, then "F10-13". I have about 19 different categories like that I would like to establish and for both genders.

Currently, my formula looks like this:

IF [age] >= 10 OR [age] <= 13 AND [gender] = "F"
THEN "F10-13"

ELSEIF [age] >10 and [age] <13 and [gender] = "M"
THEN "M10-13"
ELSEIF [age] >14 and [age] <17 and [gender] = "F"
THEN "F14-17"

ELSEIF [age] >14 and [age] <17 and [gender] = "M"
THEN "M14-17"

ELSEIF [age] >18 and [age] <21 and [gender] = "F"
THEN "F18-21"

ELSEIF [age] >18 and [age] <21 and [gender] = "M"
THEN "M18-21"

ELSEIF [age] >22 and [age] <25 and [gender] = "F"
THEN "F22-25"

ELSEIF [age] >22 and [age] <25 and [gender] = "M"
THEN "M22-25"

[etc.]

ELSE "NULL"
ENDIF

However, when I type this in, it states it as "invalid"

Please help! Thank you!

Tips and Tricks

Best Practices

Expression

Accepted answers

afv2688

Hello @HannahReed1145 ,

I re typed it a little bit but it works for me:

IF ([age] >= 10 and [age] <= 13) AND [gender] = "F"
THEN "F10-13"
ELSEIF ([age] >=10 and [age] <13) and [gender] = "M"
THEN "M10-13"
ELSEIF ([age] >=13 and [age] <17) and [gender] = "F"
THEN "F14-17"

ELSEIF ([age] >=13 and [age] <17) and [gender] = "M"
THEN "M14-17"

ELSEIF ([age] >=17 and [age] <21) and [gender] = "F"
THEN "F18-21"

ELSEIF ([age] >=17 and [age] <21) and [gender] = "M"
THEN "M18-21"

ELSEIF ([age] >=21 and [age] <25) and [gender] = "F"
THEN "F22-25"

ELSEIF ([age] >=21 and [age] <25) and [gender] = "M"
THEN "M22-25"

ELSE "NULL"
ENDIF

regards

All comments

Ladarthure

Hi @HannahReed1145,

an idea to make it a bit more simple would be to use 2 formulas, the first one on the age part with something like this:

IF [age] >= 10 and [age] <13 then '10-13'

ELSEIF [age] <= 15 then '14-15'

...

ELSE '...' endif

a second one with the gender and finally concatenate your fields, I think it might be a lot easier to handle!

afv2688

Hello @HannahReed1145 ,

I re typed it a little bit but it works for me:

IF ([age] >= 10 and [age] <= 13) AND [gender] = "F"
THEN "F10-13"
ELSEIF ([age] >=10 and [age] <13) and [gender] = "M"
THEN "M10-13"
ELSEIF ([age] >=13 and [age] <17) and [gender] = "F"
THEN "F14-17"

ELSEIF ([age] >=13 and [age] <17) and [gender] = "M"
THEN "M14-17"

ELSEIF ([age] >=17 and [age] <21) and [gender] = "F"
THEN "F18-21"

ELSEIF ([age] >=17 and [age] <21) and [gender] = "M"
THEN "M18-21"

ELSEIF ([age] >=21 and [age] <25) and [gender] = "F"
THEN "F22-25"

ELSEIF ([age] >=21 and [age] <25) and [gender] = "M"
THEN "M22-25"

ELSE "NULL"
ENDIF

regards

Dazzerman

Hi @HannahReed1145 ,

Here is another way you could do this.

If you use a Text Input tool to store your category ranges, append the values to your records, then build the category information and get rid of the surplus rows, you can change the categories however you want without changing your code.

There is a limit of 16 rows to append when using this Append Fields tool, but it will work for what you have specified.

HannahReed1145_query.yxmd

HannahReed1145

Thank you, @afv2688 ! I noticed it helped, however, I still noticed that once I type "ENDIF", suddenly my IF statement no longer counts and I get the error,

"Invalid type in operator >=." and that I have a "Parse error at char(0)". I don't think my if statement is going through at all. I added all my age ranges, however, it still does not work. Is there something wrong with my formula, or is there something wrong with my Alteryx?

IF ([age] >= 10 AND [AGE] <13) AND [GENDER] = "F"
THEN "F10-13"
ELSEIF ([AGE] >= 10 AND [AGE] <13) AND [GENDER] = "M"
THEN "M10-13"
ELSEIF ([AGE] >= 14 AND [AGE] <17) AND [GENDER] = "F"
THEN "F14-17"

ELSEIF ([AGE] >= 14 AND [AGE] <17) AND [GENDER] = "M"
THEN "M14-17"
ELSEIF ([AGE] >= 18 AND [AGE] <21) AND [GENDER] = "F"
THEN "F18-21"
ELSEIF ([AGE] >= 18 AND [AGE] <21) AND [GENDER] = "M"
THEN "M18-21"
ELSEIF ([AGE] >= 22 AND [AGE] <26) AND [GENDER] = "F"
THEN "F22-26"
ELSEIF ([AGE] >= 22 AND [age] <26) AND [gender] = "M"
THEN "M22-26"
ELSEIF ([AGE] >= 27 AND [age] <31) AND [GENDER] = "F"
THEN "F21-31"
ELSEIF ([AGE] >= 27 AND [age] <31) AND [GENDER] = "M"
THEN "M27-31"
ELSEIF ([AGE] >= 32 AND [age] <37) AND [gender] = "F"
THEN "F32-27"
ELSEIF ([age] >= 32 AND [age] <37) AND [gender] = "M"
THEN "M32-27"
ELSEIF ([age] >= 38 AND [age] <43) AND [gender] = "F"
THEN "F38-43"
ELSEIF ([age] >= 38 AND [age] <43) AND [gender] = "M"
THEN "M38-43"
ELSEIF ([age] >= 43 AND [age] <48) AND [GENDER] = "F"
THEN "F43-48"
ELSEIF ([age] >= 43 AND [age] <48) AND [gender] = "M"
THEN "M43-48"
ELSEIF ([age] >= 49 AND [age] <54) AND [gender] = "F"
THEN "F49-54"
ELSEIF ([age] >= 49 AND [age] <54) AND [gender] = "M"
THEN "M49-54"
ELSEIF ([AGE] >= 55 AND [age] <60) AND [gender] = "F"
THEN "F55-60"
ELSEIF ([age] >= 55 AND [age] <60) AND [gender] = "M"
THEN "M55-60"
ELSEIF ([age] >= 61 AND [age] <67) AND [gender] = "F"
THEN "F61-67"
ELSEIF ([age] >= 61 AND [age] <67) AND [gender] = "M"
THEN "M61-67"
ELSEIF ([age] >= 68 AND [age] <74) AND [gender] = "F"
THEN "F68-74"
ELSEIF ([age] >= 68 AND [age] <74) AND [gender] = "M"
THEN "M68-74"
ELSEIF ([age] >= 75 AND [age] <81) AND [GENDER] = "F"
THEN "F71-81"
ELSEIF ([AGE] >= 75 AND [age] <81) AND [gender] = "M"
THEN "M71-81"
ELSEIF ([age] >= 82 AND [age] <88) AND [gender] = "F"
THEN "F82-88"
ELSEIF ([age] >= 82 AND [age] <88) AND [gender] = "M"
THEN "M82-88"
ELSEIF ([age] >= 89 AND [age] <95) AND [gender] = "F"
THEN "F89-95"
ELSEIF ([age] >= 89 AND [age] <95) AND [gender] = "M"
THEN "M89-95"
ELSEIF ([age] >= 96 AND [age] <102) AND [gender] = "F"
THEN "F96-102"
ELSEIF ([age]>= 96 AND [age] <102) AND [gender] = "M"
THEN "M96-102"
ELSE "NULL"

ENDIF

benakesh

Hi @HannahReed1145 ,

This may be easier to maintain as it is bit shorter .

if [age] >= 10 AND [AGE] < 13 THEN [GENDER] + '10-13'
ELSEIF [AGE] >= 14 AND [AGE] < 17 THEN [GENDER] + "14-17"
//etc
ELSEIF [age] >= 96 AND [age] < 102 Then [gender] + "96-102"
ELSE Null()
ENDIF

Dazzerman

Hi again @HannahReed1145 ,

I modified the workflow I produced earlier to get around the maximum Append Tools issue, since you have more than 16 categories.

This method makes the maintaining of your workflow much easier, and may even introduce you to some other tools and functionality that may come in useful as you use Alteryx.

I hate complicated code myself, so try to make workflows that are easier to see what is going on, as it is less likely to cause errors.

There is more that could be done too, like only input a single column for your categories and then deduce the other number from the adjacent category. Plus have a catch-all category at the top and bottom.

I hope this is interesting and / or helpful. 🙂

HannahReed1145_query2.yxmd

Vikas27

I would like to design a code if my Book is X or Y than POD should be TRS else NULL

I'm writing code as IF [Book] = "X" OR [Book] = "Y" THEN [POD] = "TRS" ELSE [POD] = "NULL" ENDIF

But its not working. Can someone please guide?

dYoast

@Vikas27 , It is better to start a new post. More people will look at it versus a Solved post.

Your formula should be: IF [Book] = "X" OR [Book] = "y" THEN "TRS" ELSE Null ENDIF

Vikas27

Thanks, this code worked but what happened is i've tons of books alongwith let's say X and Y it has impacted all other books. I want the code to be designed that it impact only these 2 books.

IF [Book] = "X" OR [Book] = "y" THEN "TRS" ELSE Null ENDIF

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703