topic Re: Group muple rows in single row in QlikView

Group muple rows in single row

ashishpalkar — Fri, 12 Jun 2015 15:49:34 GMT

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,

Re: Group muple rows in single row

marcus_sommer — Sun, 14 Jun 2015 08:29:08 GMT

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

- Marcus

Re: Group muple rows in single row

MarcoWedel — Sun, 14 Jun 2015 09:13:07 GMT

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

Re: Group muple rows in single row

ashishpalkar — Mon, 15 Jun 2015 14:12:19 GMT

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?

Re: Group muple rows in single row

-SW- — Mon, 15 Jun 2015 14:14:52 GMT

Hi Ashish,

Have you tried using the Wrap cell text function within

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

Re: Group muple rows in single row

ashishpalkar — Mon, 15 Jun 2015 14:59:12 GMT

Hi Shun

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

Re: Group muple rows in single row

marcus_sommer — Mon, 15 Jun 2015 15:21:34 GMT

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

- Marcus

Re: Group muple rows in single row

ashishpalkar — Mon, 15 Jun 2015 15:27:08 GMT

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

Re: Group muple rows in single row

marcus_sommer — Mon, 15 Jun 2015 15:41:00 GMT

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

Re: Group muple rows in single row

ashishpalkar — Mon, 15 Jun 2015 15:47:23 GMT

Thanks Marcus for updates.

Re: Group muple rows in single row

MarcoWedel — Mon, 15 Jun 2015 23:49:14 GMT

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