IF Then Statement
- 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
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!!
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This seems to have worked perfectly! Thank you so much for your help @danilang!