Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to do this query?

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

Tags (2)
1 Solution

Accepted Solutions
yblake
Contributor II

How to do this query?

hello,

the way to go is having a aggregated calculated dimension :

aggr(count(campaign_id),customer_id)

and display expression :

count(distinct customer_id)

2 Replies
Not applicable

How to do this query?

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.

yblake
Contributor II

How to do this query?

hello,

the way to go is having a aggregated calculated dimension :

aggr(count(campaign_id),customer_id)

and display expression :

count(distinct customer_id)

Community Browser