Concatenate Multiple Columns of Data Based on One Master Column
- 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,
I have a problem I'm trying to solve. I have a that has created multiple lines of data based on the sales order and want to concatenate them together so I can match on a 1:1 sales order basis with another file. I've included an example data set below. Is this possible and if so, how would I do this at scale (considering my actual file is 50+ columns of data):
Input:
Sales Order | Country | Product | Service |
1 | USA | First | 1 |
1 | USA | First | 2 |
1 | Belgium | Second | 1 |
1 | France | Third | 1 |
1 | United Kingdom | Fourth | 1 |
2 | USA | First | 1 |
2 | USA | First | 2 |
2 | Belgium | Second | 1 |
2 | France | Third | 1 |
2 | United Kingdom | Fourth | 1 |
3 | USA | First | 1 |
3 | Belgium | Second | 1 |
3 | France | Third | 1 |
3 | United Kingdom | Fourth | 1 |
4 | USA | First | 1 |
4 | Belgium | Second | 1 |
4 | France | Third | 1 |
4 | United Kingdom | Fourth | 1 |
Output
Sales Order | Country | Product | Service |
1 | USA, Belgium, France, United Kingdom | First, Second, Third, Fourth | 1, 2 |
2 | USA, Belgium, France, United Kingdom | First, Second, Third, Fourth | 1, 2 |
3 | USA, Belgium, France, United Kingdom | First, Second, Third, Fourth | 1 |
4 | USA, Belgium, France, United Kingdom | First, Second, Third, Fourth | 1 |
Solved! Go to Solution.
- Labels:
- Preparation
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rfroese1 ,
Since you have a lot of duplicated value for all columns and that would produce an issue if done all at the same time, I needed to separated into separated summarizes.
Best,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @rfroese1 ,
Attaching workflow with desired output you wanted, We need to separate each column and then concatenate with comma separate values, because of multiple duplicate value and due to this, direct summarize-concatenate will not work.
Hopefully it will solved your problem.
Good Luck
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rfroese1
The attached should give the desired outcome. You'll need to add in a Record ID before splitting the data into three streams, and also a Sort tool after removing the Unique Values in order to make sure your data stays in the correct order when Concatenating.
If this solves your issue please mark the answer as correct, if not let me know!
Thanks!
Phil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi All,
Thank you for these responses. I'm aware of splitting the data and doing individual exercises on each column. I'm trying to see if there is a way to do multiple columns (50+) in one shot instead of having to replicate the process for each column.
Based on these responses I'm starting to think it cannot be done.
Thanks
- 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