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

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

Common Use Cases

Transformation

Accepted answers

RolandSchubert

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

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

ColumnOrder.yxmd

All comments

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

Hi @ibrahimtorunergil ,

Best regards

Roland

danilang

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

Results

ColumnOrder.yxmd

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335