AND IF Statements in Formula

[henrygeorge]

Hi Alteryx Team 

I have a file that if it meets certain criteria the number should be added by 1

Age	Indian	Amt	Type
1	Yes	28000000	Match
10	No	36000000	Match

So I placed the formula tool on my canvas and wrote this (for output column choosen as Age) - 

IF [Indian]="Yes"
AND IF [Type]="Match"
AND IF [Amt]>=20000000
THEN [Age]+1
ELSE [Age]+0
ENDIF

But I get a Data Preview as 'MAlformed If Statement'

The output required is - 

Age	Indian	Amt	Type
2	Yes	28000000	Match
10	No	36000000	Match

[ScottE]

hi @henrygeorge 

You don't need the extra "IFs" in there.  It should look like this.

if [indian]='yes' and [type]='match' and [amt]>=20000000 then [age]+1 else [age] endif

Hope that helps.

[Thableaus]

Hi @henrygeorge 

Tip: if you remove your extra IF's and just leave the first one, it might work 🙂

Cheers,

[henrygeorge]

Hi @Thableaus @ScottE 

So I applied the formula and it worked great without the extra Ifs, but when it comes to negative number

Age	Indian	Amt	Type
1	Yes	-28000000	Match
10	No	36000000	Match

and using this in formula tool 

IF [Indian]="Yes"
AND [Type]="Match"
AND [Amt]>=20000000
THEN [Age]+1
ELSE [Age]+0
ENDIF

The output arrives at 

Age	Indian	Amt	Type
1	Yes	-28000000	Match
10	No	36000000	Match

while for negative numbers above 20000000 it should add to the age. Like so -

Age	Indian	Amt	Type
2	Yes	-28000000	Match
10	No	36000000	Match

How do I arrive at this output even with negative numbers??

[markcurry · Accepted solution]

Within your formula, convert the [Amt] field to positive and use that in your condition....

 (IIF([Amt] < 0 ,[Amt] * -1, [Amt] )  >= 20000000

So ...

IF [Indian]="Yes"
AND [Type]="Match"
AND (IIF([Amt]<0,[Amt]*-1,[Amt])) >= 20000000
THEN [Age]+1
ELSE [Age]
ENDIF