Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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
Perfect!! Thank you very much.
regards,
Roberto