IF / ELSEIF / ENDIF struggle - translating Excel Nested IF Statement into Alteryx

Question

Hello all,

Getting straight into it, I am try to replicate something akin to the following Nested IF Excel statement in Alteryx.

The Excel formula is ugly but essentially saying, if the MTM is greater than 0, apply a bunch of if statements, then for those less than 0 apply a bunch of if statements.

=IF(DC10="Long",IF(CZ10=DB10,CY10,IF(ISNUMBER(DE10),DE10,IF(ISNUMBER(CY10),CY10,"No Trade"))),IF(CZ10=DB10,DA10,IF(ISNUMBER(DF10),DF10,IF(ISNUMBER(DA10),DA10,"No Trade"))))

Starting a bunch of If statements pertaining to the MTM less than 0 is what I am struggling with, i.e the bold part in the formula above. Appreciate its quite hard to visualize but hope I was clear enough to get some help. I have got to the below in Alteryx.

IF [MTM USD]>0 THEN
IF [Buy Date]=[Sell Date] THEN [Buy Price]
ELSEIF IsNumber([Adjusted Sell Price]) THEN [Adjusted Sell Price]
ELSEIF IsNumber([Buy Price]) THEN [Buy Price]
ELSE "New Trade" ENDIF.... Now how do I give conditions for all MTM<0... I am greeted with a Malformed if statement.

Thank you

ChrisHe · Accepted Answer

Hey @AboutBlank ,

Nested IF statements in AYX can be a little bit tricky.  I've attached a workflow as well but in general it would look something like this:

IF [MTM USD]>0 THEN

(IF [Buy Date]=[Sell Date] THEN [Buy Price]
ELSEIF IsNumber([Adjusted Sell Price]) THEN [Adjusted Sell Price]
ELSEIF IsNumber([Buy Price]) THEN [Buy Price]
ELSE "New Trade" ENDIF)

ELSEif [MTM USD]<0 then

(IF ... whatever you want for mtm usd <0  ENDIF)

ELSE "whatever something gets if it matches nothing"
endif

The main missing piece I often forget is that each actual IF statement itself needs an ENDIF and an ELSE somewhere.  So your statement needs at least 3 of each.  Let me know if this helps!

-Chris