topic Pivot with aggr function in QlikView

Pivot with aggr function

Wed, 06 Jan 2010 01:26:39 GMT

Hello Everybody,

I have a Table like the one below:

customer	call
A	1
B	1
C	1
D	1
C	1
D	1
A	1
B	1
C	1
D	1

Of course the pivot table is like this:

Customer	Count of call
A	2
B	2
C	3
D	3

I would like to have from the first table the following result:

Count of customer	Count of call
2	2
2	3

Coul you help me?

Thank you in advance, regards.

Pivot with aggr function

Anonymous — Wed, 06 Jan 2010 05:29:53 GMT

Hi rapozzatir,

You can aggregate the data in the script like this:


T1:
LOAD * INLINE [
Customer, Call
A, 1
B, 1
C, 1
D, 1
C, 1
D, 1
A, 1
B, 1
C, 1
D, 1
];
T2:
Load
 Customer,
 sum(Call) as CallCount
resident T1
group by Customer;
join
Load
 CallCount,
 count(Customer) as CustomerCount
resident T2
group by CallCount;

Then you have your CustomerCount and CallCount as in the posted example.

Pivot with aggr function

Fri, 08 Jan 2010 15:02:21 GMT

Thank you very much Johannes, it works!!

Just one more question: the filters do not work anymore with the new pivot. How can I use some variables to filter output data?

Thank you very much,

Roberto

Pivot with aggr function

Fri, 08 Jan 2010 16:51:14 GMT

Hi,

You can use the AGGR function to do it.

Create a dimension as aggr( sum( call ) , customer ) it's the number of call by customer

Create an expression with count(distinct customer)

after that it'd be easier do use filter.

Pivot with aggr function

Fri, 08 Jan 2010 17:54:02 GMT

Perfect!! Thank you very much.

regards,

Roberto