Question: sorting / ranking problem after using "Cross Tab" transform function

[ibrahimtorunergil]

Hi,

I have a trial balance in flat file. Columns are Account no and name, balance, and date (sorted from Jan-14 to Dec-17)

When I use "Cross Tab" function to turn my flat file into a table, column headers rank as Apr-14, Apr-15, Apr-16.... until  Sep-17 (alphabetical order rather than historical order)

I can sort my flat file by date but when I use "Cross Tab" function, order turns to alphabetical order. I need my table column headers start from Jan-14, Feb-14, Mar-14, etc. and continue in historical order. How can I solve this?

Thank you

[ConnorK]

Hi ibrahimtorunergil,

The cross-tab tool always sorts your data based on the data type that your new column header column is. In this case your column is a string based field so it will automatically sort alphabetically. However, if you transform your dates into a DateTime data type your headers will then be sorted based on time. 

Then following this transformation you can use the dynamic rename tool to rename your columns.

I hope this helps!

[RolandSchubert · Accepted solution]

Hi @ibrahimtorunergil ,

you could create a new numeric column based on "Period" (split Year and Period, calculate the new column as Year * 100 + Period  (i.e. 1-2014 -> 201401, 1-2015 -> 201501), and use this column a column header for Crosstab - it should result in the required  order. You'll need a seperate step to replace these headers by the content of the Month column using Dynamic Replace. What do you think?

Best regards

Roland

[danilang · Accepted solution]

Hi @ibrahimtorunergil 

This column alpha order behaviour of the cross tab tool is particularly vexing, but since the tool is very useful in general, we keep it around.

Here a generic technique to get around the issue.  it's based around fact that the Cross tab tool will order columns numerically if the fields names are all numbers

Start by ordering your data so the rows follow each other sequentially in the order that you want your columns to appear.  Use a mulitrow too to generate column numbers within each group of records.  Cross tab the results, but use the column number as the header field.  In parallel, get the unique list of column numbers and the corresponding header names.  Use a Dynamic Rename  configured to take the columns names from the Right Input rows.  

Results

[ibrahimtorunergil]

Hi @RolandSchubert

Thank you for your solution, it is a nice get-around. However, I couldn't use "Dynamic Replace", instead I used "Dynamic Rename". Maybe I am not that proficient in "Dynamic Replace".

A small follow-up question, as you suggest I prepared period data (i.e. 201401, 201402 all the way to 201812) in "Text Input". Actually, I prepared in excel and copy paste it... Is there anyway I can do it less manual? I have already got the list from 1-2014, 2-2014 to 12-2018 but turning it to 201401, 201402, .... 201812 is a bit problematic as there is a little zero in between (ie. 20141 and 201401).  If I use formula tool to do it [Right(string, len)+"0"+Left(string, len)], then last 3 months become like this, 2014010, 2014011, 2014012. You got what I mean....

Any idea to tackle this problem?

[ibrahimtorunergil]

Thank you @danilang for the solution. I just saw that your attached file.

[ibrahimtorunergil]

Hi @ConnorK, I changed it as you suggest it by "Select" to Datetime format, but month column (Jan-14, Feb-14) turns out to be Null. Alteryx didn't recognize I guess "Jan-14" as "01/01/2014". Any idea...?

[RolandSchubert]

 Hi @ibrahimtorunergil ,

what I actually meant was Dynamic Rename as @danilang used  in the workflow he provided.

To answer your follow up question - there is a very simple way to do this conversion. Split the field (so 1-2014 is splitted to "1" and "2014" in two seperate fields) and use there formula: ToNumber([2]) * 100 + ToNumber([1]), this will result in "201401", for 10-2014 the result will be "201410". A conversion to the header format could be done converting the number to a date and format the date. See attached workflow.

Best regards

Roland

[ibrahimtorunergil]

Thank you very much @RolandSchubert . One thing caught my attention; if I rename the 201401 data with Jan-14 data by drawing a line from - ie. - "sorting" function to right input of "dynamic rename" function, it took 5-10 min to run the flow. But when I did make an unique list and then tie to right input of "dynamic rename", it took 2-3 sec.