Multiple IF Statements with 'ToNumbers' in rows

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

delta example.xlsx

Screenshot_delta if statement.png

Screenshot_alteryx error.png

Workflow

Developer

Error Message

Accepted answers

mbarone

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])))

mbarone

@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.

binu_acs

@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

All comments

mbarone

Happy New Year!

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])))

binu_acs

@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

@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.

binu_acs

@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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703