Basic Table Tool - Changing the order of Columns (Dynamic)

[RohitJ1711]

Hi, I am using a basic table tool in my workflow and then passing the output to a render tool.

The columns here(months and yearly totals) are dynamic and change on the basis of the user input file. The input that goes into the Basic Table(I have sorted the column order before only) have the correct sequence of columns however it changes after passing through the table.

(For eg: I may run the data from Jun-15 to Jun20 but if I run it from Jan-15-Jun20 next time ,the first 6 months go in the end instead of sequential, also the yearly totals go in the end).

Please help or advise.

Thanks.

[echuong1]

The column order of your table will follow the column order in your dataset. There are a couple of options for ordering your columns dynamically.

Within the Crew Macros, there is a Field Sort tool. This allows you to dynamically sort columns in alphabetical order. If you append a prefix to each column signifying the order, this will automatically sort them for you. You can use a dynamic rename to remove the prefix.

Another option is to transpose the data so that the values are in one column. From there, you can sort the data based on the date, ascending. You can then use a cross-tab to pivot the data. The field order will be the same as the order in the un-pivoted table.

I've attached an example of the second option. Depending on how your fields are named, you may have to use the dynamic rename like I did.

[RohitJ1711]

Thank you for providing this solutions and the attached workflow, however I have already tried implementing the 2nd option that you gave and the input that passes to the Basic Table has the correct sequence. but the problem occurs when it goes to the Basic table and the newly added columns do not come in the exact sequence. If instead of passing that to an output tool instead of a Basic Table, i do get the correct sequence of columns.

[patrick_digan]

@RohitJ1711 I know I'm several months late here, but I ran into this issue today and decided that the best way to handle this issue with the Table tool is to put the table tool inside a standard macro. Inside the macro, your macro input should not include any columns that need sorted (ie just delete the columns from your macro input). Then make sure the dynamic box is checked in the table tool. This will then resort to the incoming field order since your macro input doesn't have the fields that need sorted. Now when you put your macro into you workflow and all the data flows through, it should get sorted properly since it will all be unknown to the table tool in your macro.

Let me know if you need an example worked up!

[StephenMcVeigh]

I'd be grateful if you could provide an example...think I'm having the same issue

[patrick_digan]

@StephenMcVeigh 

Attached is a lame example that demonstrates the workaround.

The key is that your macro input needs to not include any fields that are part of your sorted fields. So in my example, I have a field called "column" as well as fields labeled 2,4,6,8 in my initial data; however, my actual data may come through with fields labeled 1,2,3,4,5,6,7,8. In order for the table tool to not goof this up, I've built a macro with just the field labeled "column" and then made sure that the dynamic box is checked in the table tool in my macro. That way, all the fields will be sorted correctly. To see what goes wrong and how the macro fixes it, switch the text inputs and hit run:

It's all about the metadata. Once the table tool sees that my fields are Column, 2,4,6,8. It orders them as such and any new fields are put at the end where the dynamic box is. This doesn't work well when I add new fields: Column, 2,4,6,8,1,3,5,7. The macro trick works because the only metadata it has to build the table tool inside my macro is the Column Field (because I configured it that way). I've excluded the rest. So that way the table tool sees the numeric fields the first time at runtime and they all flow through the dynamic bucket in their correct order.

Hope that helps!

[StephenMcVeigh]

Thanks very much Patrick, would never have thought of using a macro

[star-stuff]

Thank you Patrick, I've been struggling with this one quite a bit, also haven't thought of a macro in here.