Alteryx Designer

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

Concatenate Multiple Columns of Data Based on One Master Column

Highlighted
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

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
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

Highlighted
8 - Asteroid

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

Highlighted
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

Highlighted
16 - Nebula

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

 

DavidP_0-1591195828127.png

 

 

 

Highlighted
8 - Asteroid

Hi @rfroese1 

 

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

 

Maskell_Rascal_0-1591196093461.png

 

Thanks!

Phil

Labels