ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Dynamic formula for all Level Fields

aehrenwo
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

fmvizcaino
16 - Nebula
16 - Nebula

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

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

 

 

echuong1
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

fmvizcaino
16 - Nebula
16 - Nebula

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