Dynamic Sort Table

[MD2050]

Hello Everyone-

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

 

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.

 

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. 

Will really appreciate any help. 
Thank you very much. 

MD

[T_Willins]

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.

Workflow

Join Config

Select 1 Config

Select 2 Config

[Jonathan-Sherman · Accepted solution]

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.

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]

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]

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. 

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

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