How do I change the position of the columns?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- 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!!
- 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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Tom. Option #2 worked for my very large dataset!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes.I am using the example above.
Please find the sample input and output in screenshot
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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