Concatenate all values of a single key value to one delimited value.

[ashish_2511]

Hi Community

Didn't know how to put up the heading but here's the sample data. Given below are the input table and the expected output table. Please suggest a way to achieve this.

	Input			Output
	Region	Data		Region	Data
	APAC	1		APAC	1,2,5
	APAC	2		EMEA	3,4,7
	EMEA	3		LATAM	6,8
	EMEA	4
	APAC	5
	LATAM	6
	EMEA	7
	LATAM	8

Thanks!

Sneh

[Anonymous · Accepted solution]

Hi Patnaik,

Please try the below script.

Table1:

LOAD * INLINE [
region, data
APAC, 1
APAC, 2
EMEA, 3
EMEA, 4
APAC, 5
LATAM, 6
EMEA, 7
LATAM, 8
];


NoConcatenate


table2:
load region,
Concat(data,',') as data
Resident Table1
group by region;


drop table Table1;

[Anil_Babu_Samineni]

May be using this expression in Object

Region as Dimension

And Expression is This

concat(distinct Data,',',Data)

[sdmech81]

Hi,

Please find attachment ur data is loaded in inline and showed.

used like  =aggr(Concat((Data),','),Region)

Thanks

[Anonymous]

Hi Patnaik,

Use the same expression without distinct. It works.

Take Region as Dimension and Expression is concat( Data,',')

[Anonymous]

You Should not add Data again.

[tamilarasu]

Hi Snehasis,

Script Solution:

Input:
Load Region, Concat(Data, ',') as Data Group by Region;
LOAD * INLINE [
    Region, Data
    APAC, 1
    APAC, 2
    EMEA, 3
    EMEA, 4
    APAC, 5
    LATAM, 6
    EMEA, 7
    LATAM, 8
];

Front End Solution:

Straight/Pivot table

Dimension

Region

Expression

Concat(Data,',')

[ashish_2511]

Thanks Sachin. I consider this to be the best solution from a front end perspective.

Regards

Sneh

[ashish_2511]

Thanks! Nagaraj

I personally consider this to be the best solution from a back-end perspective.

Regards

Sneh

[ashish_2511]

Thank you all for your valuable comments. I've got my answer

Regards

Sneh