Group muple rows in single row

[ashishpalkar]

Hi There

I have a below requirement, in current dashboard Data is displayed as shown below. 

Current Output        

ID	A	B	C	D	E	F	Region
708	Tjiachris,James
708		Nadeau,Brian Paul
708			Kroll,Adam Benjamin				N AMERICA
708				Huttenlocher,Richard Howard			N AMERICA
708					Huttenlocher,Richard Howard		N AMERICA
708						Chew,Pei Chin	APAC
708						Foo,Shih Wei Mark	APAC
708						Gupta,Anshumaan	APAC
708						Ikeshima,Toshihiro	APAC
708						Julienne,Lionel	EMEA
708						Kang,Rebecca	APAC
708						Kruapong,Kamontorn	APAC
708						Nguyen,Truc Linh	APAC
708						Sakti,Yogi Bima	APAC
708						Uy,Jennifer Claire Cabrera	APAC
708						Wang,Wen Bo	APAC
708							N AMERICA

We are looking to group Id's (column A) and show it on single row like below.

         Expected Output

ID	A	B	C	D	E	F	Region
708	Tjiachris,James
708		Nadeau,Brian Paul
708			Kroll,Adam Benjamin				N AMERICA
708				Huttenlocher,Richard Howard			N AMERICA
708					Huttenlocher,Richard Howard		N AMERICA
708							N AMERICA
708						Chew,Pei Chin, Foo,Shih Wei Mark, Gupta,Anshumaan, Ikeshima,Toshihiro, Kang,Rebecca, Kruapong,Kamontorn, Nguyen,Truc Linh, Sakti,Yogi Bima, Uy,Jennifer Claire Cabrera, Wang,Wen Bo	APAC
708						Julienne,Lionel	EMEA

Many Thanks,

[MarcoWedel]

Hi,

another possible solution could be:

table1:
CrossTable (IDGroup, Name, 2)
LOAD ID,
    Region,
    A,
    B,
    C,
    D,
    E,
    F
FROM [https://community.qlik.com/thread/168183] (html, codepage is 1252, embedded labels, table is @1)
Where Len(ID);

Right Join (table1)
LOAD Distinct Name Resident table1 Where Len(Name);

Left Join (table1)
LOAD Distinct
    ID,
    Region,
    IDGroup,
    Name,
    AutoNumber(RowNo(),ID&'/'&Region&'/'&IDGroup) as NameNo
Resident table1;

hope this helps

regards

Marco