Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
gayathridevi24
New Contributor III

Cumulative string Concatenation

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 :

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

 

The result should be as follows:

DEPTNO ENAME_LIST

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

 

Thanks and Regards,

Gayathri Devi.

1 Solution

Accepted Solutions
MVP
MVP

Re: Cumulative string Concatenation

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;
4 Replies
miguelbraga
Valued Contributor III

Re: Cumulative string Concatenation

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:
Screenshot_1.png
 
Hope this helps you.
 
Best regards,
MB
MVP
MVP

Re: Cumulative string Concatenation

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
New Contributor III

Re: Cumulative string Concatenation

Hi, 

Thanks for your reply.

Thanks and Regards,

Gayathri

Community Manager
Community Manager

Re: Cumulative string Concatenation

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