topic Re: Group columns in straight/pivot table in QlikView

Group columns in straight/pivot table

Mon, 01 Oct 2012 12:20:55 GMT

Hi everyone,

I'm looking to create a table (straight or pivot) that contains a grouped column. I think some example data might explain it better. The data I've loaded into QlikView contains like the below:

A	B	C	D	E
Text	More Text	Further Text	Type 1	Text
Text	More Text	Further Text	Type 2	Text
aaa	bbb	ccc	Type 1	ddd
eee	fff	ggg	Type 1	hhh
iii	jjj	kkk	Type 2	lll

I want the column D to be grouped/pivoted/etc so that the data looks like the below:

A	B	C	D	E
Text	More Text	Further Text	Type 1/Type 2 (or dropdown/twist)	Text

aaa	bbb	ccc	Type 1	ddd
eee	fff	ggg	Type 1	hhh
iii	jjj	kkk	Type 2	lll

I'm not sure whether to do this in the load of the data, or using the pivot table. I've tried using both, but I can't get the effect I want. I can make all columns pivot, but not just one.

Cheers

Re: Group columns in straight/pivot table

fosuzuki — Mon, 01 Oct 2012 20:11:49 GMT

you could do that in script using something like this:

LOAD A,

concat(D, '/') AS D,

FROM XXXXXXXX

Group by A, B, C, E;

Hope this helps.

Fernando

Re: Group columns in straight/pivot table

Tue, 02 Oct 2012 14:19:02 GMT

That doesn't work for me, but I think that's because I've also got a fairly sizeable filter in my load script:

filters(

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 6, StrCnd(null)),

RowCnd(CellValue, 8, StrCnd(null))

)),

Replace(7, top, StrCnd(null)),

Replace(6, top, StrCnd(null)),

Replace(5, top, StrCnd(null)),

Replace(4, top, StrCnd(null)),

Replace(3, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(9, top, StrCnd(null)),

Replace(10, top, StrCnd(null)),

Replace(8, bottom, StrCnd(null))

))

Column 8 is the one I want to concantanate, but it concats everything BUT column 8 when I use your method.