Column Sort Order
- 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
HI All
Im having challenge with column name rename and sort order.
I have below input data which that I parsed it from Json output file. I have 250+ columns and few of those column names have % special character which cannot be ignored.
Column Name | Column Values |
V | 100 |
AA | 200 |
C | 300 |
B | 400 |
% E | 500 |
V | 10 |
AA | 20 |
C | 30 |
B | 40 |
% E | 50 |
Expected final output should be like
V | AA | C | B | % E |
100 | 200 | 300 | 400 | 500 |
10 | 20 | 30 | 40 | 50 |
First issue: Column order
I have used multi row formula to create RowID and then used cross tab to group the column headers. When I use multirow the columns are automatically sorted in alphabetical order and % columns takes precedence.
Second issue: Column names have spaces (eg First Name ) getting replaced with "_" (First_Name_).
Column Name | Column Values |
%_E | 500 |
%_E | 50 |
AA_ | 200 |
AA_ | 20 |
B_ | 400 |
B_ | 40 |
C_ | 300 |
C_ | 30 |
V_ | 100 |
V_ | 10 |
I tried dynamic replace , Dynamic select, formula tool to replace each column with original column name but they getting sorted incorrectly because I have 250 columns so if I use sequence id appended to column names. My 11th column comes second instead of 2nd ranked column.
Column Name | Column Values | New Column |
% E | 500 | 11% E |
% E | 50 | 11% E |
AA | 200 | 2AA |
AA | 20 | 2AA |
B | 400 | 4B |
B | 40 | 4B |
C | 300 | 3C |
C | 30 | 3C |
V | 100 | 1V |
V | 10 | 1V |
Appreciate any help here! Thanks
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BK209
I would suggestion creating a new field called ColumnID then using this field as Column Headers in the Cross Tab tool. Finally use Dynamic Rename to get the column names back.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
HI
Thank you so much that helped me lot. Appreciate your help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
