AND IF Statements in Formula

Hi Alteryx Team

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

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 -

Help

Accepted answers

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

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.

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

Cheers,

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

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

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

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