Dates in crosstab out of order

[tlkirby]

I have a workflow that was created by an advanced Alteryx user and passed on to me (a beginner). I've searched the discussion boards and can't seem to find my exact situation. I have a crosstab with dates that are in the YYYY_MM_DD format, and most of the columns are in the correct order. However, there are three date columns that are showing up at the end of the output. Any ideas why this would happen? In a previous life dealing with crosstabs in Access, sometimes the columns would become fixed  but I can't find anything about that occurrence in Alteryx. 

[estherb47]

Hi @tlkirby 

The Crosstab tool will put the columns in alphanumeric order. So what I often do is assign an integer to each heading, ordered as I want them to appear in the crosstab tool.

Then, use that integer field as the crosstab header. 

You can use Dynamic Rename, and connect the data stream with both the numbers and "real" heading into the R input. Use the Take Names from Right Rows method of Dynamic Rename, and set up your columns to replace the number with the text/date.

Please let me know if you'd like a sample workflow to look at. Or you can post some data and I can work something up for you.

Cheers!

Esther

[tlkirby]

OK. So, no matter what format the dates are already in I will still need to use a different field to order the columns? A sample would be amazing!

[estherb47 · Accepted solution]

Hi @tlkirby 

Here you go! Dates need one extra prep step, which is finding the unique ones (did this with a summarize tool), and then creating the record ID on that, joining back to the original data, and performing the crosstab and dynamic rename. Unfortunately, my computer is being a total PITA and I'm unable to share a photo. Attaching a sample.

Please let me know if this makes sense/provides a solution for you.

Cheers!

Esther

[AmruthaPydeti]

Here is another solution

[Marg7]

I was searching to find a way to keep my data order after using the cross tab tool but it wasn't working. I came across your instructions and it worked Great. Just want to say Thank you for posting it.