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

Dynamic Sort Table

MD2050
8 - Asteroid

Hello Everyone-

 

I need some help in sorting dynamic data along with retaining certain column positions. For example in the snippet shown below :

 

sort.JPG

With the addition of a new month (11 in this case)  , the latest month (11) goes to very end after the "Tab" column instead of after column 10. I cam across some solution where in some authors used the FieldInfo tool although it doesn't address the point where certain columns has to be at fixed location in other words in the snippet shown below the "Total","Average", "Q1","Q2","Q3","Q4","Tab" has to come after columns 1-11. 

 

Below snippet shows what is required.

 

needed_sort.JPG

Logic i am using- in order to retain certain fixed positions of  "Total","Average", "Q1","Q2","Q3","Q4","Tab",I am assigning a number value to them like "Total"=14, "Average" = 15, "Q1"=16, etc. and then adding a sort tool to sort the field in ascending order before passing it through the transpose tool- the logic works fine past transpose tool however as soon as it goes to Table tool the column structure breaks and the new column (11 in this case) is pushed to the very end. 

Following snippet shows the logic i am using in my WF. 

 

logic.JPG

 

Will really appreciate any help. 
Thank you very much. 

MD

4 REPLIES 4
T_Willins
14 - Magnetar
14 - Magnetar

Hi @MD2050,

 

This might work for you depending on how your data comes through your workflow.  If this doesn't work you may need to use Dynamic Select tools to segregate your data then a Join or Join Multiple to combine the data.  The positioning of the Unknown field(s) is key and if it is selected or not.  If you add a field 11 to the text input at the end it does put it in the right place.  Hopefully the simple solution works for you.

 

WorkflowWorkflow

 

Join ConfigJoin Config

 

Select 1 ConfigSelect 1 Config

 

Select 2 ConfigSelect 2 Config

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @MD2050,

 

I'd approach this slightly differently and try to keep it dynamic using the dynamic select tools to save you having to manually select and deselect fields.

 

Two dynamic select tools would be needed:

Formula 1: Regex_Match([Name],'^\d+$') OR [Name] = 'Category'

Formula 2: !Regex_Match([Name],'^\d+$')

Formula 1 selects columns either named solely with digits or named Category whilst formula 2 selects columns not named solely with digits. These can then be joined back together on Category.

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

MD2050
8 - Asteroid

Gm @Jonathan-Sherman -

 

Sincere apologies for late reply. Your solution worked great, i was able to achieve the required results. 

 

Thank You very much.  

MD2050
8 - Asteroid

Hello @Jonathan-Sherman -

The logic seems not to work when i add a "Table" tool after the join tool, the sorting works great until the sorting tool as soon as the data is passed through the "Table" tool the sort order is lost. 

 

 

MD2050_0-1580943813590.png

 

 

 

Below snippet shows the data coming after the "Sort" tool before the "Table" tool .

 

MD2050_1-1580944122059.png

 

 

below is the sort order after the data is passed after "Table" tool.

 

 

MD2050_2-1580944207272.png

Labels