Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to change columns into rows and concatenate data in columns

Hi,

I have below requirement in Qliksense. Could you please help me.

I have data like below.

 

IDREGION1REGION2REGION3REGION4
1XX
2XX
3XX
4XX
5X

i need to get data as below.

  

IDREGION
1REGION1;REGION4
2REGION2,REGION3
3REGION1,REGION2
4REGION2,REGION3
5REGION4

Thanks in advance for your support.

1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

Hi Venkat,

Try this (edit connection with your connection name)

Temp:

CrossTable(Region, Value,1)

LOAD

    ID,

    REGION1,

    REGION2,

    REGION3,

    REGION4

FROM [lib://Excel\REGION.xlsx]

(ooxml, embedded labels, table is Sheet1);


FinalData:

Load ID, Concat(Region,',') As Region

Resident Temp

Group by ID;


Drop table Temp;


View solution in original post

2 Replies
Quy_Nguyen
Specialist
Specialist

Hi Venkat,

Try this (edit connection with your connection name)

Temp:

CrossTable(Region, Value,1)

LOAD

    ID,

    REGION1,

    REGION2,

    REGION3,

    REGION4

FROM [lib://Excel\REGION.xlsx]

(ooxml, embedded labels, table is Sheet1);


FinalData:

Load ID, Concat(Region,',') As Region

Resident Temp

Group by ID;


Drop table Temp;


Anonymous
Not applicable
Author

Thanks a lot Quy Nguyen.. it worked.