IF / ELSEIF / ENDIF struggle - translating Excel Nested IF Statement into Alteryx
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Chris - you are just fantastic and a testament to this community. Thank you so much for taking the time to create a flow - so far it looks like exactly what I need. Superb!
