Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
... 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
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
... 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
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;
Thanks guys. Got it. Using concat as an expression works!