topic How to do this query? in QlikView

How to do this query?

Sat, 16 May 2009 00:34:22 GMT

Hello,

I have the follow situation:

table: CUSTOMER
customer_id | customer_name

table: CAMPAING
campaing_id | campaing_name

table: PARTICIPATION
campaing_id | customer_id

Many customers can participate of many campaing, righ?

I need a chart that returns:

numofcampaings | qttyofusers
1 | 15320 - number os persons that participated of just 1 campaing
2 | 650 - number os persons that participated of 2 campaings
3 | 120 - number os persons that participated of 3 campaings
4 | 25 - number os persons that participated of 4 campaings

Anyone can help me?

thank you

How to do this query?

Sat, 16 May 2009 02:33:30 GMT

SELECT customer_id, customer_name, Count(campaign_id) As campaign_count

FROM customer, participation

WHERE customer.customer_id = participation.customer_id

GROUP BY customer_id, customer_name

You could use that in place of loading the Customer table by itself. You would still probably want to load the Campaign and Participation tables as are. Then in your expressions, use Set Analysis to get the counts.

Sum({<campaign_count = {1}>}, 1, 0) would be the number that participated in 1 campaign.

The above query is Oracle SQL, here's T-SQL is you prefer:

SELECT customer_id, customer_name, Count(campaign_id) As campaign_count

FROM customer INNER JOIN participation ON customer.customer_id = participation.customer_id

GROUP BY customer_id, customer_name

I'd also expect that this could be done by simply loading your tables individually and then using expressions to do all the dirty work.

How to do this query?

yblake — Sat, 16 May 2009 16:52:04 GMT

hello,

the way to go is having a aggregated calculated dimension :

aggr(count(campaign_id),customer_id)

and display expression :

count(distinct customer_id)