Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jiwaniakbiar
Contributor II
Contributor II

Help with Data Model

I am republishing this question with more details:

I have following 3 tables

MProgram

ProgramCenterIDFeesVenueDate
1300Venue120 sept 2018
2400Venue 221 sept 2018
3200Venue 325 sept 208
4250Venue 425 sept 2018
5100Venue 526 sept 2018

MParticipant

ParticipantIDFirst NameMiddle NameLast NameDOB
1F1M1L124 Jan 1995
2F2M2L223 Dec 1985
3F3M3L313 Oct 1999
4F4M4L414 Oct 1993
5F5M5L523 Apr 2001
6F6M6L624 Aug 2003
7F7M7L724 Dec 2005
8F8M8L825 Apr 2008
9F9M9L926 Aug 2010
10F10M10L1026 Dec 2012

MProgramParticipant

ProgramCenterIDParticipantID
11
12
13
21
23
25
22
33
34
35
31
41
42
43
44
52
53
54
55

When I upload the these tables to Qlik Sense, following associations are created:

1. MProgram.ProgramCenterID <> MProgramParticipant.ProgramCenterID

2. MParticipant.ParticipantID <> MProgramParticipant.ParticipantID

When I display Count of ParticipantID, it displays 10. Where as I want to display count of participants who attended atleast 1 program. i.e. 5

I also want to display Total number of Participants. i.e. simply non-distinct count of rows in table MProgramParticipant

I can't figure out how to do this. Any thoughts?

3 Replies
rubenmarin

Hi Akbar, can you try?

I want to display count of participants who attended atleast 1 program. i.e. 5

Count({<ProgramCenterID={"*"}>} Distinct ParticipantID) // Participants related with any programCenterID


I also want to display Total number of Participants. i.e. simply non-distinct count of rows in table MProgramParticipant

Count(ProgramCenterID) // It will count all rows, it's supposed that ProgramCenterID and ParticipantID always have a value in each row

jiwaniakbiar
Contributor II
Contributor II
Author

This works

Count({<ProgramCenterID={"*"}>} Distinct ParticipantID) // Participants related with any programCenterID


Also found another expression which worked for the above:

Count({<[ID-ProgramCenterId] -= NULL >}[[ParticipantID])


However, This doesn't work

Count(ProgramCenterID) // It will count all row

rubenmarin

Hi Akbar, I will need more info about what is not working. What's the expected result and what'ss happening, better if you can upload a sample that shows the problem.