topic Re: Pivot table as in excel in QlikView

Pivot table as in excel

felcar2013 — Thu, 10 Jan 2013 15:12:04 GMT

i need to create a pivot table in qlikview with 2 dimensions as rows and 2 dimensions (with granularity) as columns. It is not possible to do it wiht the Chart options. I dragged and dropped already, but i cannot do that with two dimensions and besides, i get the dimension name as a column without data in it.

are there other options to create a pivot table like the ones in excel?

thanks

felipe

Header 1	Header 2	Header 3	Header 4	DIMENSION 3	DIMENSION 3	DIMENSION 4	DIMENSION 4
	DIMENSION 1	DIMENSION 2	Num Member (EXPRESSION)	MALE	FEMALE	GRANULARITY 4.1	GRANULARITY 4.2
	total	2009	VALUES	::...	.	..	..
..	total	2010
	online	2009
	online	2010
							VALUES

Re: Pivot table as in excel

montero91 — Thu, 10 Jan 2013 16:44:53 GMT

Hi felcar,

you have the example of table required.

Because what I understand you want a straight table like your example and not a pivot, in straight table can move the dimensions and expressions anywhere except as crosstab.

Re: Pivot table as in excel

felcar2013 — Thu, 10 Jan 2013 17:56:38 GMT

Manuel

thanks for your help

see attached the table as i want. i want to show two dimensions fields as columns.

felipe

Re: Pivot table as in excel

montero91 — Thu, 10 Jan 2013 18:54:10 GMT

Hi Felcar.

I guess the problem is Age and Gender Distribution as dimensions that you want.

In the script you can add calculated fields,

Example

in the load

If(Age >= 0 and Age <=15, count(Age)) as [0-15],
If(Age >= 16 and Age <=25, count(Age)) as [16-25],

And for the Age Distribution

If(Age >=0 and Age <= 60, ‘Age Distribution’ as Title

With this calculation are as values that can be used as dimensions, since Qlikview can only do basic calculations in dimensions.

Or the other is that using tags, and you make a visual game for the table.

Re: Pivot table as in excel

felcar2013 — Thu, 10 Jan 2013 18:58:31 GMT

it is a good approach

i try it

thanks

felipe

Re: Pivot table as in excel

felcar2013 — Thu, 10 Jan 2013 19:00:28 GMT

it is a good approach

i try it

thanks

felipe

Re: Pivot table as in excel

felcar2013 — Thu, 10 Jan 2013 19:00:32 GMT

it is a good approach

i try it

thanks

felipe

Re: Pivot table as in excel

felcar2013 — Tue, 15 Jan 2013 12:25:56 GMT

i tried this in the script and it did not work,

Load

contact_Age,
if(contact_Age>=18,count(contact_Age)) as '[18 - 29]',

....

from Table 1;

it seems not to recognize the "count (contact_Age)"

Re: Pivot table as in excel

felcar2013 — Tue, 15 Jan 2013 12:26:12 GMT

i tried this in the script and it did not work,

Load

contact_Age,
if(contact_Age>=18,count(contact_Age)) as '[18 - 29]',

....

from Table 1;

it seems not to recognize the "count (contact_Age)"

Re: Pivot table as in excel

felcar2013 — Tue, 15 Jan 2013 12:28:08 GMT

i tried this in the script and it did not work,

Load

contact_Age,
if(contact_Age>=18,count(contact_Age)) as '[18 - 29]',

....

from Table 1;

it seems not to recognize the "count (contact_Age)"

Re: Pivot table as in excel

montero91 — Tue, 15 Jan 2013 14:36:16 GMT

Hi Felcar.

If you want to make the sum or count, you must use the "group by". The basic operations between fields b*a, a+b, a-b, etc. They directly. but count (a) or sum (B) you need "group by" some dimension.

and the us without ' only [

Example:

Load

region

contact_Age,
if(contact_Age>=18,count(contact_Age)) as [18 - 29],

....

from Table 1

Group by region

;

or if you want only a switch ' 1 or 0 ' to after summing them

Example:

Load

region

contact_Age,
if(contact_Age>=18,1,0) as [18 - 29],

....

from Table 1;

Re: Pivot table as in excel

felcar2013 — Tue, 15 Jan 2013 15:09:43 GMT

Thanks for the answer, as i have many dimensions to group by, i did as you told me the first time, and finally it worked. I created the if statements per age group and then added the count([agegroup]) in the chart expression for every new created [ageGroup]

felipe

Re: Pivot table as in excel

felcar2013 — Tue, 15 Jan 2013 15:17:52 GMT

felipe