Cumulative string Concatenation

[gayathridevi24]

Hi All,

I am trying to do cumulative strings but not able to solve. Could anyone please help me to solve this issue.

Consider the following as the Source data :

DEPTNO	ENAME
20	G
10	A
10	D
20	E
10	B
10	C
20	F
20	H

The result should be as follows:

DEPTNO ENAME_LIST

10	A
10	A,B
10	A,B,C
10	A,B,C,D
20	E
20	E,F
20	E,F,G
20	E,F,G,H

Thanks and Regards,

Gayathri Devi.

[miguelbraga]

Hey there,

Your script should look like this:

temp1:
LOAD *,
 DEPTNO&ENAME as KeyOrder
;
LOAD * INLINE [
    DEPTNO, ENAME
    20, G
    10, A
    10, D
    20, E
    10, B
    10, C
    20, F
    20, H
];

temp2:
NoConcatenate
LOAD  
  DEPTNO,
   ENAME
Resident temp1
Order by KeyOrder ASC
;

left join

LOAD
  DEPTNO,
  Concat(distinct ENAME,',',DEPTNO) as ENAME1
Resident temp2
Group by DEPTNO;

Table:
LOAD
  DEPTNO,
  Left(ENAME1, index( ENAME1, ENAME )) as ENAME_LIST
Resident temp2;

Drop Table temp1, temp2;

And the result will be like this:

Hope this helps you.

Best regards,

MB

[tresesco · Accepted solution]

A bit more simplified code:

temp1:
LOAD * INLINE [
    DEPTNO, ENAME
    20, G
    10, A
    10, D
    20, E
    10, B
    10, C
    20, F
    20, H
];
temp2:
LOAD 	
		DEPTNO,
 		If(DEPTNO=Peek(DEPTNO),
 		Peek(NewEname)&','&ENAME, ENAME) as NewEname
Resident temp1
Order by DEPTNO, ENAME ASC;
Drop Table temp1;

[gayathridevi24]

Hi, 

Thanks for your reply.

Thanks and Regards,

Gayathri

[Sue_Macaluso]

Although this has responses I'd like to get to correct product group for others to see. Is this QlikView or Qlik Sense.