Concatenate Multiple Columns of Data Based on One Master Column

[rfroese1]

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

[fmvizcaino]

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

[rupali_bhise]

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

[Maskell_Rascal]

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

[rfroese1]

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

[DavidP · Accepted solution]

I've decided to go with a different method. I think this scales better when you have lots of columns.

[Maskell_Rascal]

Hi @rfroese1 

Here is a more dynamic model that doesn't split your data. 

Thanks!

Phil