Formula tool for Asset Classification

[jyeung753]

Hello, 

I am currently trying to develop a formula in Alteryx which will help identify a variety of asset classes and assign them a correlating FAS level. 

(Ex. Warrant = "1", Corporate Bond = "2", Manager Priced = "3", etc.)

Essentially the data I am receiving, gives all asset classes aside from cash a FAS level of 9. These all need to be replaced with their respective/correct levels.

I have attached the sample data and workflow as reference for what is being used for analysis. 

Thanks

[terry10]

@jyeung753   There are several ways to accomplish this in Alteryx.

1. Try the Switch function:  Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.
2. You can expand the IF-ELSEIF-THEN that you started to cover all of the FairValueHierarchyLevels
3. The way I would do it is to create a lookup table of the distinct InvestmentTypeCode and FAS, then JOIN your data to the lookup table. This solution allows easy modification of values or addition of InvestmentTypeCodes.

[jyeung753]

Hi @Terry  , 

Firstly I appreciate the input. I actually created a separate thread regarding the third option you provided, I was trying to make a mapping table to solve this exact issue I am currently having. Would you possibly be able to expand on the lookup table option? I'm still learning the in's and out's of Alteryx so I am not so certain on the structure of what that workflow would look like. Attached is a sample workflow I just created. 

Would you mind providing input if I am headed in the right direction with this? I'm struggling getting the workflow to output all the data. 

Thank you!

[NMangera]

@jyeung753,

if you want to explore option 2. as described @terry10, the if statement would look something like 

[terry10]

@jyeung753 

You are SO close! Just change the field you JOIN on to "InvestmentTypeCode" instead of the level fields and you will get the desired results.

[jyeung753]

@terry10 

Okay, once I make that adjustment it seems the data types are an issue. Not sure which if InvestmentTypeCode should be switched to Double? or Vice Versa. 

[jyeung753]

@NMangera 

Thank you for the input! Currently working through option 3. However I have saved out a formula tool identical to yours. 

By chance - if Cash and other asset types (which aren't classified a FAS level) need to be assigned "N/A" in the data. How would I go about this in the formula tool?

I tried including those lines, however it seems I can't return string types in the same output field since those FAS levels are typically numeric values. 

Thanks!

[terry10 · Accepted solution]

@jyeung753 

Join InvestmentTypeCode to InvestmentTypeCode. Both are strings.

[NMangera · Accepted solution]

@jyeung753,

spot on. Fields need to be of a consistent data type, i.e. either string or numeric. To work around this, a new field can be created with the data type set to V_WString. The logic of the IF statement has also been amended to capture other asset types.

[jyeung753]

@terry10 @NMangera 

Both options seem to work well with my workflow. Thank you both for the input and solutions, going to mark these as solved now!