Combine 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
Hi, I'm trying to complete an exercise but I couldn't seem to make it work. What I wanted to output is two columns for Exp and Dis with the corresponding row values in order from 1-9.
Exp Dis
1513 28,959
403 1315
.. ..
.. ..
I'm attaching an input sample for reference, appreciate the help.
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Start by using a Transpose Tool and leaving all of the Dis and Exp fields as your data fields.
From here, you can use a formula tool to label each item as Dis or Exp and also label the row number. Make a new field, name your new field [Dis/Exp], and make your expression "IF Contains([Name],"Dis") THEN "Dis" ELSE "Exp" ENDIF". And another expression onto the formula, name it [Row Number] and make the expression "Right([Name],1)" (if you go beyond 1-9 and have two digit numbers you'll need to update this).
Finally, use a crosstab tool. Group on [Row Number], change column headers to [Dis/Exp], and select [Value] as the values option. Select "Sum" as your aggregation.
I believe this should do the trick. Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It did, thanks for the help!