IF Then Statement

[grsomer]

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"
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" elseif Uppercase(Left([Product Description], 7)) = "DEPOSIT" AND [TRADE_KEYWORD.BO_ProductType]="Cash Margin" THEN "09995" elseIF Uppercase(Left([Product Description], 7)) = "DEPOSIT" AND [TRADE_KEYWORD.BO_ProductType]!="Cash Margin" THEN "09890" else "80495" ENDIF

However, this formula is not producing the result I would expect. Is there a better way to recreate this function/something to be changed in my existing syntax?

Thank you!!

[danilang · Accepted solution]

hi @grsomer 

In the original from excel, the Cash_Margin calculation is completely nested inside the "then" clause of the Left(UpperCase(...

You should be able to reproduce that with 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" 
elseIF LEFT(UPPERCASE([Product Description]),7)="DEPOSIT" then
	IF [TRADE_KEYWORD.BO_ProductType]="CASH_MARGIN" then
		"09995"
	else
		"09890"
	endif 
elseIF LEFT(UPPERCASE([Product Description]),4)="LOAN" then 
	IF [TRADE_KEYWORD.BO_ProductType] ="CASH_MARGIN" then 
		"04995" 
	else 
		"04890"
	endif
else 
	"80495" 
endif

Dan

[grsomer]

This seems to have worked perfectly! Thank you so much for your help @danilang!