Pivot with aggr function

[#1]

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.

[Anonymous]

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.

[#3]

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

[#4 · Accepted solution]

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.

jj

[#5]

Perfect!! Thank you very much.

regards,

Roberto