Alteryx designer Discussions

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

Sort fields according to date

Highlighted
5 - Atom

Hi,

 

I'm new to Alteryx so bear with me. I have lots of fields on the format name (monthyear), e.g. "january2019", "february2019", "march2019" etc. What I have done so far is to load the data by using the Asterix symbol (since all excel files contains the month and year), then check for output file name as field, used the text to columns tool appropriately and finally the cross tab tool. However, the fields are not sorted according to the correct dates. As an example, "february2019" appears before (i.e. to the left for) "january2019". I know that you can use the select tool and move fields up/down, but is there a smarter way to tell alteryx to sort the fields with dates according to my fields' name format? In this case there were not that many fields, but let's say there were 100+ fields, then it would be expedient if I could tell Alteryx to sort them by using some kind of tool.

 

Help is much appreciated!

 

Regards, PC

Highlighted
Alteryx Certified Partner

I would sort your dates in their column (before the cross tab) and rename them so january2019 -> 1_january2019 and february -> 2_february2019. Then once you've crosstabbed you can remove the number and underscore using regex or a trim, etc. Example attached,

 

workflowworkflow

 

Highlighted
8 - Asteroid

I am attempting to apply the same logic to my model but when I use the dynamic rename, it changes my columns headers to _field. Any thoughts on why this is happening?

 

Cbennett022_0-1586787640767.png

 

Cbennett022_1-1586787658727.png

 

Labels