How to change columns into rows and concatenate data in columns

[Anonymous]

Hi,

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

I have data like below.

ID	REGION1	REGION2	REGION3	REGION4
1	X			X
2		X	X
3	X	X
4		X	X
5				X

i need to get data as below.

ID	REGION
1	REGION1;REGION4
2	REGION2,REGION3
3	REGION1,REGION2
4	REGION2,REGION3
5	REGION4

Thanks in advance for your support.

[Quy_Nguyen · Accepted solution]

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]

Thanks a lot Quy Nguyen.. it worked.