Formula tool for Asset Classification

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

Sample_Workflow.yxmd

Sample_Data1.xlsx

Datasets

Developer

Developer Tools

Output

Data Investigation

Accepted answers

terry10

@jyeung753

Join InvestmentTypeCode to InvestmentTypeCode. Both are strings.

Capture.PNG

Sample_Workflow (3).yxmd

NMangera

@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.

Formula tool for Asset Classification.yxmd

All comments

terry10

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

Try the Switch function: Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.
You can expand the IF-ELSEIF-THEN that you started to cover all of the FairValueHierarchyLevels
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!

Sample_Workflow.yxmd

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.

Lookup_Table.PNG

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!

NMangera

@jyeung753,

Formula tool for Asset Classification.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703