Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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
Highlighted
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
Highlighted
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