Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Highlighted
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
Community Browser