Merge multiple rows into one row
- 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
I have 4 data sets I am needing to merge into a single row based on the tracking #. Each data set contains a field or fields that have different data I need to compile into one record. Below is an example of what I have and what I am attempting to do. I think I am over complicating my attempt to merge.
This is what my messy solution looks like...
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The summarize tool has some functionality that can help with this. You can group by the field that you want one row for each value, and then the others you can use a combination of “first” if you just want the first value or “concatenate” if you want all of the values put into one cell for each group of values in that column. You can also use functions like max or min if you need the largest or smallest values if there are various different ones.
- 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
Alteryx is SO beautiful...I knew there was a simpler way 😃
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Glad I could help! There are plenty of Solution Engineers like myself as well as talented ACE individuals, Alteryx Partners, and enthusiasts that love to answer Community questions in our free time. Thank you for being a valuable part of the Alteryx Community!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BrandonB,
Can I continue to get help on this question after I accepted your solution?
I'm able to get most of the records to merge but some are not, and I cannot see a pattern for what I need to do.
There are 6 sets in the screenshot grouped by color. The first 5 showing datasets that did not merge and the last group that did merge.
I concatonate the Dataset name to show when the records have merged.
I grouped by all strings and selected MAX for dates and amounts. There are 36 fields so I'm not showing all them in this screenshot of the Summarize configuration:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I am pretty new on Alteryx, but I think it is now being able to group where the rows has an empty value.
So you should clean it first, or you should remove them after the first group.
If you are able to send some part of the database we can try to figure out it better.
Let us know if it helps 🙂
Regards,
Marcos
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The issue is likely in some of your Group By selections where there are multiple values across rows. This will result in a separate row for each. You can think of group by as saying "Make a row for each combination of Group by values that exists and perform the other operations on each group (Min/Max/etc).
Field 1 | Field 2 | Field 3
A | Apple | 1
A | Apple | 2
A | Asparagus | 6
A | Asparagus | 8
B | Banana | 2
B | Banana | 2
C | Carrot | 4
C | Carrot | 6
C | Carrot | 7
Group by Field 1
Group by Field 2
Max Field 3
Would give you the following as a result
A | Apple | 2
A | Asparagus | 8
B | Banana | 2
C | Carrot | 7
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @msouriques and @BrandonB! It was a combination of both of these. I had records where one had a value and the other was null, and also when I did a group by when there were different values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have the same type of thing going on in my dataset, and I had no idea that a Summarize could concatenate items, this really helps in combining the multiple/duplicate rows with separate comments. I have found through that I needed 2 summarize items to be able to 1 - group by, then 2 - concatenate.