Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to use multiple IF statements with multiple AND statements

HannahReed1145
6 - Meteoroid

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!

12 REPLIES 12
Ladarthure
14 - Magnetar
14 - Magnetar

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
16 - Nebula
16 - Nebula

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
11 - Bolide

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

HannahReed1145
6 - Meteoroid

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
11 - Bolide

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
6 - Meteoroid

@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
12 - Quasar

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 

benakesh_0-1573747508377.png

Dazzerman
11 - Bolide

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

NikhilVedula
6 - Meteoroid

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!

 

Labels