Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Dynamic formula for all Level Fields

Highlighted
8 - Asteroid

I have the following formula.

 

 

[LEVEL01]+"," +[LEVEL02]+"," + [LEVEL03]+"," +[LEVEL04]+"," +[LEVEL05]+"," +LEVEL06+"," +LEVEL07+"," +[LEVEL08]+"," +[LEVEL09]+"," +[LEVEL010]+"," +[LEVEL011]+"," +[LEVEL012]+"," +[LEVEL013]

 

 

I would like to have this formula be able to adjust if data changes and there are new columns such as Level014 and Level 015.  Right not these fields are not appearing in the crosstab being created because they have no data but that could change tomorrow. 

 

Any suggestions? 

 

Adam

Highlighted
Alteryx Certified Partner

Hi @aehrenwo ,

 

There are many ways to do that, but I need to understand a little more about your workflow in order to provide the better solution for you. Would you be able to share at least a print and how the data is coming for this formula, please?

 

Best,

Fernando Vizcaino

Highlighted
8 - Asteroid

aehrenwo_0-1575309142593.png

 

it is coming from a Cross tab tool... let me know if this helps. 

 

the original data is transposed another field is joined then it is put back together in a cross tab to create the new data. Only fields where there is at least one value are getting created which is why I need the formula to adjust if it changes in the future. 

 

 

Highlighted
Alteryx
Alteryx

You can use a combination of cross tab and transpose to achieve this. You would essentially use a transpose tool to get all of the values into one column. You can then use a cross tab tool with the concatenate method to combine values separated by commas. See attached for an example. Let me know if this helps! 

 

echuong1_0-1575309937657.png

Highlighted
Alteryx Certified Partner

Hi @aehrenwo .

 

Yes, that helped! 

One thing that you can do is to create a control column to automatically cross-tab everything that you need in the cross tab already as attached example.

 

fmvizcaino_0-1575310084052.png

 

Let me know if that works for you.

Best,

Fernando Vizcaino

Labels