How to order columns by any given line values?

Question

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 Product316122408788

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

Sum_A Product316Sum_B Product122Sum_C Product4087Sum_D Product88

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

Sum_C Product4087Sum_A Product316Sum_B Product122Sum_D Product88

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

C Product4087A Product316B Product122D 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 Product316122408788

...instead of getting this:

C ProductA ProductB ProductD Product408731612288

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!

Column reordering problem example.yxmd

MarqueeCrew · Accepted Answer

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 Answer

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,