Group text from multiple rows into a single column, comma separated

[#1]

Hi Qlik-ers,

I was wondering if something like this is possible :

Sample table that I have :

Name	Company	Product Code	Value
Sean	Company A	DCA1	20
		FTY2	40
		TUG4	70
		SLU5	10
James	Company B	PSR2	80
		YTI5	100
		POL4	10

What I would like to achieve :

Name	Company	Product Code	Value
Sean	Company A	DCA1, FTY2, TUG4, SLU5	140
James	Company B	PSR2, YTI5, POL4	190

I would like to concatenate the Product into a single row separated by a comma for each Name and Company.

Appreciate your help.! thanks.

Shan.

[MarcoWedel]

Hi Shan,

try

LOAD
  Name,
  Company,
  Concat([Product Code], ',') as [Product Code],
  Sum(Value) as Value
Group By Name, Company;
LOAD Name,
    Company,
    [Product Code],
    Value
FROM
[http://community.qlik.com/thread/115379]
(html, codepage is 1252, embedded labels, table is @1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));

regards

Marco

[MarcoWedel · Accepted solution]

... or more dynamic on the front end side:

LOAD Name, 
     Company, 
     [Product Code], 
     Value
FROM
[http://community.qlik.com/thread/115379]
(html, codepage is 1252, embedded labels, table is @1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));

regards

Marco

[maxgro]

source:

load

  if(len(trim(Name))=0, peek(Name), Name) as Name,

  if(len(trim(Company))=0, peek(Company), Company) as Company,

    [Product Code],

    Value;   

load

  Name,

    Company,

    [Product Code],

    Value

FROM

[http://community.qlik.com/thread/115379]

(html, codepage is 1252, embedded labels, table is @1);

table:

NoConcatenate load

  Name, Company,

  concat([Product Code], ', ') as [Product Code],

  sum(Value) as Value

Resident source

group by Name, Company;

DROP Table source;

[#5]

Thanks guys. Got it. Using concat as an expression works!