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 OrderCountryProductService1USAFirst11USAFirst21BelgiumSecond11FranceThird11United KingdomFourth12USAFirst12USAFirst22BelgiumSecond12FranceThird12United KingdomFourth13USAFirst13BelgiumSecond13FranceThird13United KingdomFourth14USAFirst14BelgiumSecond14FranceThird14United KingdomFourth1 Output Sales OrderCountryProductService1USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1, 22USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1, 23USA, Belgium, France, United KingdomFirst, Second, Third, Fourth14USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1

Concatenate Multiple Columns of Data Based on One Master Column

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

Tips and Tricks

Preparation

Accepted answers

DavidP

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

Concatenate Columns.yxmd

All comments

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

2020-06-03 Concatenate Multiple Columns of Data Based on One Master Column.yxmd

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

Concatenate Multiple Columns.yxmd

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

Concatenate Multiple Columns of Data Based on One Master Column.yxmd

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

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

Concatenate Columns.yxmd

Maskell_Rascal

Hi @rfroese1

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

Thanks!

Phil

Concatenate Multiple Columns of Data Based on One Master Column.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703