How to use multiple IF statements with multiple AND statements

[HannahReed1145]

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!

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

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]

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

[Dazzerman]

Hi @HannahReed1145 

I copied your text and pasted it into a Formula and it worked for me.

The error message sounds like you haven't set the Data Type to a string format.  Take a look at the bottom of the window for your formula.  I used V_WString, with the default size, but you could use others that use less memory

[HannahReed1145]

@Dazzerman 

thank you so much for getting back to me. I have had it set to a string format, however, I found that one of my errors was not setting "age" to be numeric. I had it as a V_Wstring. A silly mistake on my part, but thank you so much for helping me out!!

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

[NikhilVedula]

Hi all,

I have 3 SQL files one from Oracle, Teradata and the other from MS SQL, I need to filter them accordingly

based on names and send them to  formula tool to do the rest.

My question is how can i filter them at a time with  3 conditional statements with in a single tool, to send them

to each formula tool to do further action.

If possible please let me know.

Thanks in Advance!