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,

[marcus_sommer]

Try as expression something like: concat(YourValue, chr(10))

- Marcus

[MarcoWedel]

Hi,

one solution using a pivot table, ID, IDGroup an Region as dimensions and some similar expression to the one, Marcus already proposed:

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
LOAD Distinct Name Resident table1 Where Len(Name);

hope this helps

regards

Marco

[ashishpalkar]

Thanks Its working using Concat()..

One thing i observed , i can see data in one single row. Is there any way to wrap text like excel?

[-SW-]

Hi Ashish,

Have you tried using the Wrap cell text function within

     Chart Properties, Presentation, Wrap Cell Text (checkbox) ?

[ashishpalkar]

Hi Shun

Yes I have tried using Wrap Cell Text, it will wrap all rows and when data varies the exceeding characters wont show. 

[marcus_sommer]

What do you mean with wrap text like in excel? Could you post a screenshot.

- Marcus

[ashishpalkar]

Hi Marcus

Please find example below, all grouped rows showing as wrap text, other rows showing as it is without any change.

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

[marcus_sommer]

Ok. I understand but unfortunately there is no possibility to have flexible column-widths or row-heights - you need to set them fix, unless you used an extension. I have no idea if there is one pivot-extension available which you could customize.

- Marcus

[ashishpalkar]

Thanks Marcus for updates.