Conversion of Excel three nested IF statement to Alteryx

[Hamster]

I have the follow Excel IF statement that I need translated in Alteryx.

=IF(H6="CASH",IF(AB6<0,"CRO","CGO"),IF(AB6<0,"ORO","OGO")).

I've been able to get this far but it's returning a Parse Error at Char(135).

IF [A]='CASH' THEN (IF [B]<0 THEN 'CRO' ELSE 'CGO'
endIF)
else
(if [B]<0 THEN 'ORO' ELSE 'OGO' endIF)

Any help would be appreciated.

[BrandonB]

I think that something like this might work for you

IF [A]='CASH' AND [B]<0

THEN 'CRO'

ELSEIF IF [A]='CASH' AND [B]>=0

THEN 'CGO'

ELSEIF [AB]<0

THEN 'ORO'

ELSE 'OGO'

ENDIF

[PaulNo · Accepted solution]

Hi @Hamster,

You have done all the work so congrats!

You are missing the "endif" to match the initial "if" 🙂

Thanks,

PaulN

[Hamster]

Hi PaulN

If I add the EndIF after the last ')' I get another error message saying Invalid type in operator <.

This is the formula now.

IF [a]='CASH' THEN (IF [b]<0 THEN 'CRO' ELSE 'CGO'
endIF)
else
(if [b]<0 THEN 'ORO' ELSE 'OGO' endIF) endif

[RolandSchubert · Accepted solution]

Hi @Hamster ,

I think, it's a data type related problem - is [b] a numeric field (INT, DOUBLE)? Using ToNumber([b]) should solve this.

Try:

IF [a]='CASH' THEN
   IF ToNumber([b]) < 0 THEN
      'CRO'
   ELSE
      'CGO'
   ENDIF
ELSE
   IF ToNumber([b]) < 0 THEN
      'ORO'
   ELSE
      'OGO'
   ENDIF
ENDIF

Btw: The brackets are not needed, 

Let me know if it works for you now.

Best,

Roland

[Hamster]

Thanks Roland - [b] was a calculated field but had String as it's data type, changing this to double fixed my formula.