Excel Formula conversion into Alteryx Expression

[MarkPitcock]

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

EntityId

EntityName

RestrCd

Currency

SeniorityCd

Type

Att

Exh

Width

Weight

Pre_Event_Factor

Post_Event_Factor

Days

Recovery

Entity

Short_Entity

209601

LCDX.NA-12.26: 5YR

NR

USD

SR_SEC

Index

0

100

1

0.01

0.73

0.72

6

100%

MetroPCS Wireless, Inc.

LCDX.NA-12.26: 5YR

209602

CDX.NA.HY.7-V11: 3YR Tranche 10-15%

NR

USD

SR_SEC

Tranche Mezz

10

15

0.05

0.01

1

1

6

100%

MetroPCS Wireless, Inc.

CDX.NA.HY.7 Tranche 10-15%

209603

CDX.NA.HY.7-V11: 3YR Tranche 0-10%

NR

USD

SR_SEC

Tranche 0-X%

0

10

0.1

0.01

1

1

6

100%

MetroPCS Wireless, Inc.

CDX.NA.HY.7 Tranche 0-10%

210301

CDX.NA.HY.7-V11: 3YR Tranche 35-100%

NR

USD

SR_SEC

Tranche X-100%

35

100

0.65

0.01

0.81

0.794615385

6

100%

MetroPCS Wireless, Inc.

CDX.NA.HY.7 Tranche 35-100%

[CharlieS · Accepted solution]

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:

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

[MarkPitcock]

Thanks CharlieS will attempt this