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

Concatenate Multiple Columns of Data Based on One Master Column

rfroese1
6 - Meteoroid

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 OrderCountryProductService
1USAFirst1
1USAFirst2
1BelgiumSecond1
1FranceThird1
1United KingdomFourth1
2USAFirst1
2USAFirst2
2BelgiumSecond1
2FranceThird1
2United KingdomFourth1
3USAFirst1
3BelgiumSecond1
3FranceThird1
3United KingdomFourth1
4USAFirst1
4BelgiumSecond1
4FranceThird1
4United KingdomFourth1

 

Output

Sales OrderCountryProductService
1USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1, 2
2USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1, 2
3USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1
4USA, Belgium, France, United KingdomFirst, Second, Third, Fourth1

 

6 REPLIES 6
fmvizcaino
17 - Castor
17 - Castor

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.

fmvizcaino_0-1591193063787.png

 

 

 

Best,

Fernando Vizcaino

rupali_bhise
8 - Asteroid

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
13 - Pulsar

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. 

 

Maskell_Rascal_0-1591194527178.png

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

rfroese1
6 - Meteoroid

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
17 - Castor
17 - Castor

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

 

DavidP_0-1591195828127.png

 

 

 

Maskell_Rascal
13 - Pulsar

Hi @rfroese1 

 

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

 

Maskell_Rascal_0-1591196093461.png

 

Thanks!

Phil

Labels