Multiple IF Statements with 'ToNumbers' in rows

[sureslala]

Hi 
Firstly happy New Year!, secondly I am trying to create a new column using a multiple 'if statement' referencing numbers in rows but I'm getting a 'parse error at char (250) malformed if statement error'.

In the attached screenshot you can see what the excel formula I'm trying to re-create in Alteryx. I'm sure there is a longer way to do this using true/false statements however think I'm pretty close to nailing this in a formula.

IF [Contract Code] = "Stock ledger" OR "Warrant Ledger" THEN ToNumber([Amount (Base Units)]) && IF [Contract Code] = "Cash Ledger" OR "FX Cover" OR "FX Forward" OR "Usance LC" OR [Contract Group1] = "Cash" THEN "0" ELSE ToNumber([Future Delta]) ENDIF

I've attached screenshots of my Alteryx error, excel formula I'm trying to recreate and sample data. Really appreciate a helping hand pls, feel like I'm on the right track, hopefully.

Thanks

Sures

[mbarone · Accepted solution]

Happy New Year!

I prefer to use boolean conditionals (less typing!), but feel free to use IF/THEN/ELSE/ENDIF/etc.  The "or" is translated into an "IN" statement for Alteryx.  Otherwise, you're not too far off at all, good job!

This should do it - again, I prefer the boolean IIF(x,y.z), which says "if x, then y, else z".  And you can nest them until your heart's content:

IIF([Contract Code] IN ('Stock ledger','Warrant Ledger'),ToNumber([Amount (Base Units)]),

IIF([Contract Code] IN ('Cash Ledger','FX Cover','FX Forward','Usance LC') OR [Contract Group] IN ('Cash'),0,

ToNumber([Future Delta])))

[binuacs]

@sureslala I agree @mbarone , you may also remove the coma separator to avoid the error in if statement

If [Contract Code] In ("Stock Ledger","warrant ledger") Then ToNumber(Trim(ReplaceChar([Amount (Base Units)],",","")))
ElseIf [Contract Code] In ("CASH LEDGER","FX Cover","FX Forward","Usance LC","Cash") Then 0
Else ToNumber(Trim(ReplaceChar([Future Delta],",","")))
EndIf

[sureslala]

Hi mbarone
Thank you for your response, works perfectly and is easy to understand, thank you very much!. Still not 100% on when I use  round brackets after 'IIF' - suppose when writing multiple statements? 

Cheers
Sures

[sureslala]

Hi binuacs
Thanks for your reply, I've decided to go for the boolan solution as it was easy to follow, however being new to Alteryx I'm interested in when you use certain code and what it represents/does, in particularly

ToNumber(Trim(ReplaceChar - what does this code do?

",","")))  - what does this represent/do?

Appreciate your help with this, thanks!
Sures

[mbarone · Accepted solution]

@sureslala - round brackets: I'm assuming you mean parentheses like this:   (   and )

Remember the format is IIF(X, Y, Z)

So after every IIF you need a "("  and for every one of those, you'll need a ")" when you close the IIF statement.  

Examples:

1. IIF(X, Y, Z)

2.  IIF(X, Y, 

     IIF(Z, A, 

     IIF(B, C,

     D)))

In number 2, you have one main IIF and 2 nested, for a total of 3 IIFs.  So you need to close with 3 ")".   Rectangular brackets [ ] are for variables in your formula.

[binuacs · Accepted solution]

@sureslala 

ToNumber(Trim(ReplaceChar - what does this code do? - If you notice your amount has coma in it and it is also string type, so the function ReplaceChar remove the coma from the amount field, basically it just replace the coma with "", means if you  have the amount 1,000 after applying the ReplaceChar(1,000,",","") you will get the result as 1000

The Trim Function is for removing extra leading or preceding spaces from the the give field 

The ToNumber field convert the given field from String to Numeric

[binuacs]

@sureslala  If you are new to Alteryx I would prefer go with normal If Else because it is easy to understand , the Boolean IF sometime confusing if you have more conditions in it, also missing a bracket would cause error which will be difficult for you to identify. Once you are family with the normal If else you can go with Boolean IF