Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to order columns by any given line values?

Bruno_Pasquini
7 - Meteor

Greetings, ALLteryx fellows!

 

I have to change the order of columns of a list of products given their grand totals descending order...

 

I'll use here a 4 products list example, but my real problem has 88 products so I cannot afford to rearrange columns order manually... This list can even be bigger in the future...

 

For instance, if after summing up their values I do get:

Sum_A ProductSum_B ProductSum_C ProductSum_D Product
316122408788

 

Using the Transpose tool I can transpose this 2x4 table into a 4x2 table:

Sum_A Product316
Sum_B Product122
Sum_C Product4087
Sum_D Product88

 

Then I sort the numbers' column in descending order using the Sorting tool:

Sum_C Product4087
Sum_A Product316
Sum_B Product122
Sum_D Product88

 

Then I use the Multi-line Formula tool just to wipe out the "Sum_" prefix of variables.

C Product4087
A Product316
B Product122
D Product88

 

Nice and easy so far...

 

Ok, time to transpose back... I found out this to be easier to be done with the Cross Tab tool.

 

HOWEVER, as I do so, it reorders columns by its alphabetical order, not the totals' order I have just sorted...

 

I get...

A ProductB ProductC ProductD Product
316122408788

 

...instead of getting this:

C ProductA ProductB ProductD Product
408731612288

 

What am I doing wrong?

 

I know, there probably is a solution whose simplicity can be annoying, but I simply was unable to find it.

 

Can you please help me finding it?

 

Attached is the flow which I just described above...

 

Thanks in advance!

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @Bruno_Pasquini,

 

Funny that you bring that up.  On my backlog is the creation of a macro to give users more control of the order of fields ( @NeilR ).  The way that I'd handle this is to use a RECORD ID.  There are multiple ways to update the field name.  You might try this.

 

RecordID is a STRING 01 - 99

Name = RecordID + Name

Now you'll have 01Sum_C Product

 

Once you cross tab (it will be in order), you can remove the prefix using a dynamic rename Regex_Replace([_CurrentField_],"(\d+)(.*)",'$2')

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Thableaus
17 - Castor
17 - Castor

Hi @Bruno_Pasquini 

 

This is probably one of the most annoying things about Cross-Tab Tool (also, the underscore transformation stinks as well).

 

But here's an adapted solution:

 

shdje.PNG

- Add a record ID to give columns an order

- Use Formula Tool to add Record ID to your Field Name.

- Pivot your New Name column with Cross-Tab tool

- Dynamic Rename all of your fields with Regex formula, extracting the number and underscores with this:

Replace(REGEX_Replace([_CurrentField_], "\d+(.*)","$1"), "_", " ")

 

 

Attached workflow on version 2018.4

 

Cheers,

Bruno_Pasquini
7 - Meteor

@Thableaus wrote:

Hi @Bruno_Pasquini 

 

This is probably one of the most annoying things about Cross-Tab Tool (also, the underscore transformation stinks as well).

 

But here's an adapted solution:

 

shdje.PNG

- Add a record ID to give columns an order

- Use Formula Tool to add Record ID to your Field Name.

- Pivot your New Name column with Cross-Tab tool

- Dynamic Rename all of your fields with Regex formula, extracting the number and underscores with this:

Replace(REGEX_Replace([_CurrentField_], "\d+(.*)","$1"), "_", " ")

 

 

Attached workflow on version 2018.4

 

Cheers,


Thank you, both @MarqueeCrew and @Thableaus !

 

There was indeed a solution whose simplicity can be annoying after all!

 

Cheers!

Labels