Concatenate Columns based on Arbitrary number of Label Columns

[MarkBelisle]

Hi all,

I'm looking for a way to create a concatenated column based on an arbitrary number of "Label" columns which are brought in through an input file.

e.g.

Input

ID	Label_1	Label_2	...	Label_n	other columns
1	x	a	anything	<null>	Dog
2	y	b	anything	q	Cat
3	x	b	anything	w	Birb

Output

ID	Labels	other columns
1	x, a, ...	Dog
2	y, b, ..., q	Cat
3	x, b, ..., w	Birb

I won't know how many Label columns there are, but there will always be at least 1. Ideally concatenated by comma delimited, and Null values should be ignored. I can remove the "Label" columns myself.

Any column that doesn't start with "Label" can be ignored, but should remain in the data set.

[Felipe_Ribeir0 · Accepted solution]

Hi @MarkBelisle 

One way of doing this dynamically

[Felipe_Ribeir0]

Unfortunately, to do it dynamically the only ways that i know about are by transposing the data or using a iterative macro.

These methods can demand to much computing power with large datasets. IF you also feel that the multi field formula could be enhanced to do this job, please vote for this idea :)

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhancement-on-the-Multi-Field-formula/idi-p...

[MarkBelisle]

1) That the Multi-Field formula could be able to evaluate a set of columns dynamically and generate just one new column (the sum of the evaluated columns, the concatenation of it...).

I agree. My first thought was using multi field formula; something like:

IF Contains([_CurrentFieldName_], "Labels") THEN

[_Concatenated_Labels_] = [_Concatenated_Labels_] + ", " + [_CurrentField_]

ELSE ...

ENDIF

Is there a way to modify a different field in the same row than the one currently being checked by Multi-Field? I believe that would solve the problem..

[Felipe_Ribeir0]

@MarkBelisle unfortunately, i am afraid that you wont be able to solve this with the multi field formula. I am 99% certain about this, but i would be very happy if someone else could show us a way to do this 😁

[MarkBelisle]

Indeed. I was really hoping it was possible with the multi field too.

I've accepted your solution - the dataset I'm running on should remain small enough that processing won't become an issue. I've upvoted your aforementioned Enhancement as well.

Thank you for your help!