How to order columns by any given line values?

[Bruno_Pasquini]

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 Product	Sum_B Product	Sum_C Product	Sum_D Product
316	122	4087	88

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

Sum_A Product	316
Sum_B Product	122
Sum_C Product	4087
Sum_D Product	88

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

Sum_C Product	4087
Sum_A Product	316
Sum_B Product	122
Sum_D Product	88

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

C Product	4087
A Product	316
B Product	122
D Product	88

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 Product	B Product	C Product	D Product
316	122	4087	88

...instead of getting this:

C Product	A Product	B Product	D Product
4087	316	122	88

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!

[MarqueeCrew · Accepted solution]

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

[Thableaus · Accepted solution]

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:

- 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]

---

@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:

- 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!