Solved: Pivot table as in excel - Qlik Community

felcar2013 · ‎2013-01-10

Hi

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

montero91 · ‎2013-01-15

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;

View solution in original post

montero91 · ‎2013-01-10

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.

felcar2013 · ‎2013-01-10

Manuel

thanks for your help

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

felipe

montero91 · ‎2013-01-10

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.

felcar2013 · ‎2013-01-10

it is a good approach

i try it

thanks

felipe

felcar2013 · ‎2013-01-10

it is a good approach

i try it

thanks

felipe

felcar2013 · ‎2013-01-10

it is a good approach

i try it

thanks

felipe

felcar2013 · ‎2013-01-15

hi

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)"

felcar2013 · ‎2013-01-15

hi

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)"

felcar2013 · ‎2013-01-15

hi

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)"

Pivot table as in excel

Related Topics