We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Formula tool for Asset Classification

jyeung753
7 - Meteor

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

9 REPLIES 9
terry10
11 - Bolide

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

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
10 - Fireball

@jyeung753,

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

 

NMangera_0-1683823830379.png

 

 

terry10
11 - Bolide

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

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

@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
11 - Bolide

@jyeung753 

 

Join InvestmentTypeCode to InvestmentTypeCode. Both are strings.

NMangera
10 - Fireball

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

 

NMangera_0-1683826066302.png

 

 

jyeung753
7 - Meteor

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

Labels