Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel Formula conversion into Alteryx Expression

MarkPitcock
7 - Meteor

Hello All, 

 

I'm struggling to convert what feels like to me complex excel formula into an Alteryx Expression. I've tried and failed miserably using the formula tool to create the expression but I feel like the solution is using this tool. 

 

Below is my data set along with excel formula for each field I am trying to convert. I'll have to be mindful of the data type used in Alteryx for this to work . Hopefully you have enough information to create these expression

 

I could really do with someones expertise to help me convert this. I believe fields Att and Exh would need to be populated before Type as there is a dependency. 

 

Much appreciated. 

 

Type: =IF(J2=1,"Single Name",IF(ABS(G2)>0,IF(ABS(H2)<100,"Tranche Mezz","Tranche X-100%"),IF(ABS(H2)<100,"Tranche 0-X%","Index")))

 

Att:=IF(ISERROR(LEFT(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)),FIND("-",MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)))-1)),0,LEFT(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)),FIND("-",MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)))-1))

 

Exh:=IF(ISERROR(MID(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)),FIND("-",MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)))+1,LEN(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8))))),100,MID(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)),FIND("-",MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)))+1,LEN(MID(B2,FIND("Tranche",B2)+8,(FIND("%",B2))-(FIND("Tranche",B2)+8)))))

 

Post Event Factor: =MAX(0,IF(F2="Single Name",1,IF(F2="Index",K2-J2,IF(F2="Tranche 0-X%",((K2*I2)-(J2*(1-N2)))/I2,IF(F2="Tranche X-100%",((K2*I2)-(J2*N2))/I2,IF(F2="Tranche Mezz",IF(K2<>1,((K2*I2)-(J2*(1-N2)))/I2,1)))))))

 

 

ABCDEFGHIJKLMNOP
EntityIdEntityNameRestrCdCurrencySeniorityCdTypeAttExhWidthWeightPre_Event_FactorPost_Event_FactorDaysRecoveryEntityShort_Entity
209601LCDX.NA-12.26: 5YRNRUSDSR_SECIndex010010.010.730.726100%MetroPCS Wireless, Inc.LCDX.NA-12.26: 5YR
209602CDX.NA.HY.7-V11: 3YR Tranche 10-15%NRUSDSR_SECTranche Mezz10150.050.01116100%MetroPCS Wireless, Inc.CDX.NA.HY.7 Tranche 10-15%
209603CDX.NA.HY.7-V11: 3YR Tranche 0-10%NRUSDSR_SECTranche 0-X%0100.10.01116100%MetroPCS Wireless, Inc.CDX.NA.HY.7 Tranche 0-10%
210301CDX.NA.HY.7-V11: 3YR Tranche 35-100%NRUSDSR_SECTranche X-100%351000.650.010.810.7946153856100%MetroPCS Wireless, Inc.CDX.NA.HY.7 Tranche 35-100%
2 REPLIES 2
CharlieS
17 - Castor
17 - Castor

Hi @MarkPitcock 

 

First, let's talk about field references. I assume in your example below that the field names (like "EntityId") are your field names and that the "A" Excel position references are just there for our formula reference. If this isn't the case, we can talk more about that.

 

Regarding the expressions, Alteryx supports several ways that we could rebuild these formula. The easiest here would be to use IIF( in places where IF( is used and the structure remains the same ("IIF(<Condition>,<True>,<False>)"). Finally, we'll need to update the field references. Alteryx will reference using the field names like [Att] instead of position like "G". Also, the row number reference is automatically built into this type of expression. Here's how your first expression could look in Alteryx:

 

IIF([Weight]=1,"Single Name",IIF(ABS([Att])>0,IIF(ABS([Exh])<100,"Tranche Mezz","Tranche X-100%"),IIF(ABS([Exh])<100,"Tranche 0-X%","Index")))

 

In a Formula tool, I set the field this is applied to as "Type" and use that as the expression:

20200622-ExcelIFConversion1.PNG

 

This is what I would recommend to get this process up and running in Alteryx. 

MarkPitcock
7 - Meteor

Thanks CharlieS will attempt this 

Labels