community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Large Nested IF Then Statement

Asteroid

Hi! 

I am trying to recreate the excel function: 

("BJ2:BJ" & i, _
"=IF(AX2=""REPO_REV"",""80390""," & _
"IF(AX2=""REPO"",""80400""," & _
"IF(AX2=""FEES_BBL_ACCR"",""06299""," & _
"IF(AX2=""REPO_BSB"",""80395""," & _
"IF(LEFT(UPPER(E2),7)=""DEPOSIT"",IF(AX2=""CASH_MARGIN"",""09995"",""09890"")," & _
"IF(LEFT(UPPER(E2),4)=""LOAN"",IF(AX2=""CASH_MARGIN"",""04995"",""04890"")," & _
"""80495""))))))")

 

The statement is a long one but I believe it is saying: if col AX = "Reopo Rev" then "80390" elseif AX = "Repo" then "80400" else if AX= "Fees BBL ACCR" then "06299" elseif AX = "Repo BSB" then "80395" elseif the first 7 characters of col E = "Deposit" then (If AX = "Cash Margin" then "09995" else "09890") elseif the first 4 characters of col E = "Loan" then (if AX = "Cash Margin" then "04995" else "04890") else "80495". 

I am unsure of how to recreate this formula in Alteryx, particularly the last 2 conditions.

 

So far my syntax looks like this: if [TRADE_KEYWORD.BO_ProductType] = "REPO_REV" then "80390" elseif [TRADE_KEYWORD.BO_ProductType] = "REPO" then "80400" elseif [TRADE_KEYWORD.BO_ProductType] = "FEES_BBL_ACCR" THEN "06299" ELSEIF [TRADE_KEYWORD.BO_ProductType] = "REPO_BSB" THEN "80395"
else "80495" endif. 

 

Is there a way to add in the last two conditions into this syntax?

Thank you!

Alteryx
Alteryx

@grsomer 

 

The syntax is going to be very similar in Alteryx

 

 ELSEIF Uppercase(Left([TRADE_KEYWORD.PO_ProductType], 4)) = "LOAN" THEN ...
Asteroid

Hi @MichalM 

Simply adding this into my existing syntax returns the error "Malformed If statement". 

My syntax now is: if [TRADE_KEYWORD.BO_ProductType] = "REPO_REV" then "80390" elseif [TRADE_KEYWORD.BO_ProductType] = "REPO" then "80400" elseif [TRADE_KEYWORD.BO_ProductType] = "FEES_BBL_ACCR" THEN "06299" ELSEIF [TRADE_KEYWORD.BO_ProductType] = "REPO_BSB" THEN "80395"
elseif Uppercase(Left([Product Description], 4)) = "LOAN" THEN (if [TRADE_KEYWORD.BO_ProductType]="Cash Margin" then "04995" else "04890") else "80495" endif

Alteryx
Alteryx

@grsomer

 

It's the below nested IF that's causing it

elseif Uppercase(Left([Product Description], 4)) = "LOAN" THEN (if [TRADE_KEYWORD.BO_ProductType]="Cash Margin" then "04995" else "04890")

 

Try to split the conditions out instead

 

elseif Uppercase(Left([Product Description], 4)) = "LOAN" AND [TRADE_KEYWORD.BO_ProductType]="Cash Margin" THEN "04995" else "04890"

 

Asteroid

@MichalM This syntax does not allow for other conditions to be added/ to continue the formula. Would it be best to create separate formulas for this function?

Alteryx
Alteryx

You can add the other condition like this

 

elseif Uppercase(Left([Product Description], 4)) = "LOAN" AND [TRADE_KEYWORD.BO_ProductType]="Cash Margin" THEN "04995" 
elseif Uppercase(Left([Product Description], 4)) = "LOAN" AND [TRADE_KEYWORD.BO_ProductType]!="Cash Margin" THEN "04890"
else "04890"
endif
Labels