Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Merge multiple rows into one row

sonseeahray
8 - Asteroid

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.

 

sonseeahray_0-1578156774773.png

 

This is what my messy solution looks like...

sonseeahray_1-1578156851756.png

8 REPLIES 8
BrandonB
Alteryx
Alteryx

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.

sonseeahray
8 - Asteroid

Hi @BrandonB,

 

Let me give that a try with one of the fields. Thanks

sonseeahray
8 - Asteroid

Alteryx is SO beautiful...I knew there was a simpler way 😃

BrandonB
Alteryx
Alteryx

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!

sonseeahray
8 - Asteroid

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. 

sonseeahray_3-1578246288500.png

 

 

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:

sonseeahray_2-1578245674070.png

 

msouriques
6 - Meteoroid

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

BrandonB
Alteryx
Alteryx

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

 

sonseeahray
8 - Asteroid

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.

Labels