How do I change the position of the columns?

[JohnDoe]

How can I change the position of the columns? Some of the columns I have to move come from my input file, some others I create myself with the multi-field and the formula tool, but I have to move quite a few of them around. I haven't been able to find any documentation on this. Any ideas? Thanks

[MarqueeCrew · Accepted solution]

Use a SELECT tool.

You can highlight a row or a set of rows and then use the UP & DOWN arrows at the top of the configuration window to move the fields/columns of data.  You can also RENAME the fields if needed and/or change the data types during this update.

The select tool is wonderful!  It is so wonderful that the same functionality can be found in some other tools too (e.g. JOIN).

Hopefully, this answers your question.  If not, I'd be happy to help more.

Mark

[RodL]

Mark has the correct process.

One thing he didn't mention that some might not know is you can also select the set of rows and then use the 'right click' to drag them where you want them to go.

[tom_montpool]

Another trick...

Let's say you have 25 variables and you want to move variable 3 to come after variable 20.

You can select variable 3 and click the down arrow...what, 16 times?...tedious.

You can select variable 3 and use the right-click move...OK if it all fits in the window.

You can select variables 4 through 20 and click the up arrow once...miraculous!!

[tom_montpool]

The Select tool is great when you've got a limited number of columns. If you have a large number, moving them one-at-a-time is tedious and prone to error.

There are two ways that I've used to work with this:

1. Edit the workflow directly, outside Alteryx.
• The YXMD is XML, so if you insert a Select tool, you can find it using the ToolID and then edit the sort order.
• Be sure to edit the <OrderChanged value="False" /> tag
• Save a backup of the workflow in case you muck something up!!
2. Use Dynamic Rename, Select, and Dynamic Rename again.
• You'll need an external file that has the columns in your dataset (COLUMNS) and the order you want your columns in
• Add a second field with the column list prefixed by the sort order (padded) (ORDERED_COLUMNS)
• Use this file in Dynamic Rename with "Take Field Names from Right Input Rows"
• In the Select tool, choose "Sort>On Field Name"
• Use Dynamic Rename and reverse the settings to get back to the original column names.

I've attached a simple illustration of method two.

Also, Adam Riley made a macro to do the Sort by Field Name which you can find on his blog (http://www.chaosreignswithin.com)

[Freeclimber]

Thanks Tom. Option #2 worked for my very large dataset!

[Krish]

I wonder if it works for following dataset.When I tried I was not getting sorted list as expected.

COLUMNS	ORDER	ORDERED_COLUMNS
A	1	01A
B	2	02B
C	3	03C
D	4	04D
E	5	05E
F	6	06F
G	100	100G
H	8	08H
I	23	23I
J	10	10J
K	11	11K

[Freeclimber]

From that dataset, I would expect the order of A, B, C, D, E, F, H, J, K, I, G.

What order are you getting?

Also, are you using the example workflow from above?

[Krish]

Yes.I am using the example above.

 Please find the sample input and output in screenshot

[tom_montpool]

Remember that text (including numbers formatted as text) and numbers sort differently.

If you're not sure how it will end up, you can sort your 'renaming' field.

In your example, if you sort on "ORDERED_COLUMNS" as-is, you will get A-F,H,G,J,K,I

If you modify the field -- PadLeft([ORDERED_COLUMNS],4,'0') -- you will get A-F,H,J,K,I,G

If you modify it so there are no leading zeros -- TOSTRING([ORDER])+[COLUMNS] -- you will get G,J,K,A,I,B-F,H