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

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.

Reporting

Accepted answers

All comments

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.

New Workflow1.yxmd

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.

star-stuff

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

SathishVijayagopalan

Thank you, Patrick! I was struggling with this for quite sometime. You have been a savior.

vivaisun11

Hi @patrick_digan.

I haven't built macros before, and I am unable to modify your workflow to correctly capture my dynamic column input.

Can you please help me with this?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335