IF Statement using less than

[Carlyn]

Hi, I am trying to incorporate an if statement that finds a specific set of data and it includes amounts less than 6,000,000. 

The type for the amount column is double but when I try to put in the formula without the quotes it doesn't seem to like it.

if [Cod.Op.Bco.] = "2033" and [Descripción] = "IVA Basico 21%" THEN "2"
ELSEIF [Cod.Op.Bco.] = "66" AND [Descripción] = "IVA Basico 21%" AND [IMPORTE] <-6,000,000 AND [CUENTA] = "10202160000" THEN "X" ENDIF

If I add the quotes in the formula is accepted, but it doesn't give me the correct result.

if [Cod.Op.Bco.] = "2033" and [Descripción] = "IVA Basico 21%" THEN "2"
ELSEIF [Cod.Op.Bco.] = "66" AND [Descripción] = "IVA Basico 21%" AND [IMPORTE] <"-6,000,000" AND [CUENTA] = "10202160000" THEN "X" ENDIF

Any ideas what I am doing wrong?

[GMG0241]

Don't include the commas in the less than. Your less than should be [IMPORTE] < -6000000

[davidskaife]

Hi @Carlyn 

Two things jump out to me, remove the comma's in the 6,000,000 and you're missing a final ELSE clause, try this:

IF [Cod.Op.Bco.] = "2033" and [Descripción] = "IVA Basico 21%" 
THEN "2"
ELSEIF [Cod.Op.Bco.] = "66" AND [Descripción] = "IVA Basico 21%" AND [IMPORTE] <-6000000 AND [CUENTA] = "10202160000" 
THEN "X" 
ELSE ""
ENDIF

Replace the "" with what ever you want it to be for the final ELSE.

Remember a multiple IF statement in Alteryx is IF...THEN...ELSEIF..THEN...ELSE..ENDIF

[Carlyn]

sorry, I was just sharing a part of the overall formula so didn't show the correct ending.

if [Cod.Op.Bco.] = "2033" and [Descripción] = "IVA Basico 21%" THEN "2"
ELSEIF [Cod.Op.Bco.] = "66" AND [Descripción] = "IVA Basico 21%" AND [IMPORTE] < -6000000 AND [CUENTA] = "10202160000" THEN "X" ELSE [REF] ENDIF

I have just realised that my descripcion was wrong, so its now resolved.

Thanks for your help

When I try to remove the "" it doesn't like the formula, all the text after [IMPORTE] is black, but when I add back in the "" it turns green and pink the way I would expect it to. Really confused why its not working

[GMG0241]

Are you able to send over your workflow? Even with dummy data so we can see what's going wrong? The fact that it's still going black implies that there's a data type issue, but you obviously mentioned that it's a double - might be worth double checking that the [IMPORTE] column input to the formula tool is a double, and that you're not redefining it as a string or something in the formula tool.

[Carlyn]

Thanks so much for responding. It was the issue with my descripcion that was causing the problem. removing the "" works fine now. thanks again