Combine rows into 5

[JDong]

Hi Team,

In Alteryx how do I combine these rows ? As noticed it creates duplicate rows and we just need SubName columns so eventually there will be only 5 rows

Col1	Type	Name	SubName
Company1	A	Apple
Company1	B	Apple
Company1	C	Apple
Company1	D	Apple
Company1	E	Apple
Company1	A	Apple	CET
Company1	B	Apple	CET
Company1	C	Apple	CET
Company1	D	Apple	CET
Company1	E	Apple	CET

Thanks

[RolandSchubert]

Hi @JDong ,

what exactly do you mean by "combine"? The Summarize tool could be an option (e.g. Group By Col1, Type and Name and Concatenate SubName). Could you explain in more detail, what result you expect?

Best,

Roland

[JDong]

Thanks

Company1	A	Apple	CET
Company1	B	Apple	CET
Company1	C	Apple	CET
Company1	D	Apple	CET
Company1	E	Apple	CET

Expected outcome

[RolandSchubert · Accepted solution]

So I think, the Summarize tool will be helpful. But - it seems, you want to keep the rows with SubName (if available). So you can use group by as already explained and "MAX" for SubName.I've attached a sample workflow. What do you think?

[JDong]

Thanks. What if I need to left join this result with another say 20 columns. Will it again duplicate and the left join will be a combination of numbers and strings.

[RolandSchubert]

Hi @JDong ,

if you can join on all fields (Col1, Type, Name, SubName) there should be no duplication, this will only happen, if you join only on e.g. Col1, Name and SubName (same value for all rows, so each record will join to each record of the other dataset and the result will be 5 records for each matching row.

Best,

Roland

[JDong]

Thanks the left table will not have all the join columns but have just the one ID column to join. Can I use unique after the join ?

[RolandSchubert]

I'm still not quite sure what you want to achieve. As I understood, you have one table with 20 columns including an ID column. You want to join this to new second table with columns Col1, Type, Name and SubName, I guess, to add additional columns. The main questions is, which of the columns in table 2 ( Col1, Type, Name and SubName) is available in table 1 and can be used to join. Depending on the data, a unique may make sense. Could you explain the relation between the tables in more detail or provide sample data? Will be much easier then.