topic Re: Group text from multiple rows into a single column, comma separated in QlikView

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

Wed, 23 Apr 2014 23:31:47 GMT

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.

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

MarcoWedel — Wed, 23 Apr 2014 23:40:21 GMT

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

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

MarcoWedel — Wed, 23 Apr 2014 23:50:42 GMT

... 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

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

maxgro — Wed, 23 Apr 2014 23:54:11 GMT

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;

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

Thu, 24 Apr 2014 03:30:30 GMT

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