Combining Similar Columns and Group by/Sum by multiple 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 am getting some input data as below:
Name 1 | Name 2 | Group 1 | Group 2 | ValueA 1 | ValueA 2 | ValueB 1 | ValueB 2 |
XXX | 1 | 100 | 200 | ||||
YYY | 2 | 100 | 200 | ||||
XXX | 1 | 300 | 300 |
Note that if Name doesn't have a value than the respective columns afterwards will be null. (i.e. Name 1 is null then null in Group/ValueA/ValueB 1)
and I would like to combine columns based on the numbering while summing the values:
Name | Group | ValueA | ValueB |
XXX | 1 | 400 | 500 |
YYY | 2 | 100 | 200 |
I've tried using Transpose and Cross Tab but run into trouble as the Value column will have both string and number formatted columns. So how can I group by combining several string columns and sum multiple columns?
Thanks in advanced for any help and please let me know if the question is unclear.
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@aaggh2003
Just as you mentioned, there are both string and number formatted columns, so we have to seperate them and I use a Regex filter.
And I use record ID the keep tracking the records before I can join them togher.
In your real data, it can be much complex for removing the postfix in column names.
Hope it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, @aaggh2003
Another way for you , maybe this is a dynamic solution:
******
If can help you get your want, please mark it as a solution and give a like for more share.
Otherwise please upload sample data for get advanced way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I added a new field in the formula by assigning an unique group_id for each _X, and uses both record_id and group_id during the cross tab tool, all good now!
Thanks everyone!
-------------------
I realized I misled you all by showing an input that did not have data in _1 and _2 at the same time, which is actually possible and with the cross tab tool having "First" value makes those _2 values disappear.
See attached for the sample record, which got values in all columns, and the output I am looking for should be:
Name | Group | Sum_ValueA | Sum_ValueB |
250 | Other | 0.01 | 5772.57 |
104 | Other | 12500 | 237.45 |
I tried creating multiple transpose tools while adding numbers to create new record id and use Union tool at the end but I actually have 10 columns for each header, not sure if this is the best way to do it.
Apology for making the confusion.
