Select Columns based on Formula?

[jreinhart]

So I have 2 sets of data, one being the actual data like issue age, sex, policy number etc. then I need to add duration to it based on certain criteria. If(Sex=Sex and Iss age = Issage) and My calculated duration =8 use column Dur 8.

This is what I'm trying to pull from looks like.

So I need to use certain columns based on what I calculate because I could calculate any duration 1-100. How do I do If(this) use column Dur 1 elseif(that) use column Dur 2. etc. etc. Is there an easy way to do this in alteryx? I found out a solution using transposes, but I don't really like how hardcoded I made it. Any advice would be appreciated

[NicoleJohnson · Accepted solution]

I believe you could do this with a Join + Transpose + Filter...

1. Join your dataset with the Duration columns to your second data set, joining on Sex = Sex and Iss Age = IssAge

2. Transpose your resulting joined data, using Sex, Iss Age, Duration, and any other fields you want to keep for each line as Key Fields, and then selecting all the "Dur #" fields as your Data Fields

3. Filter the data using the following formula to find only those "Dur #" fields that match your calculated duration (formula will remove the "Dur " part of the column name that is now in the [Name] field, and then look to see if the remaining value matches your CalcDuration value):

ToString([CalcDuration]) = Replace([Name],"Dur ","")

I've attached a sample workflow... If this doesn't accomplish what you're trying to achieve, please let us know! Perhaps some sample data of the before & after desired outcome would help if more assistance is needed...

Cheers!

NJ