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.

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

RohitJ1711
5 - Atom

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.

7 REPLIES 7
echuong1
Alteryx Alumni (Retired)

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.

 

echuong1_0-1602854358091.png

 

RohitJ1711
5 - Atom

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
17 - Castor
17 - Castor

@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
5 - Atom

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

 

patrick_digan
17 - Castor
17 - Castor

@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:

patrick_digan_0-1616427521262.png

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
5 - Atom

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

 

star-stuff
5 - Atom

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

Labels