- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help with Data Model
I am republishing this question with more details:
I have following 3 tables
MProgram
ProgramCenterID | Fees | Venue | Date |
1 | 300 | Venue1 | 20 sept 2018 |
2 | 400 | Venue 2 | 21 sept 2018 |
3 | 200 | Venue 3 | 25 sept 208 |
4 | 250 | Venue 4 | 25 sept 2018 |
5 | 100 | Venue 5 | 26 sept 2018 |
MParticipant
ParticipantID | First Name | Middle Name | Last Name | DOB |
1 | F1 | M1 | L1 | 24 Jan 1995 |
2 | F2 | M2 | L2 | 23 Dec 1985 |
3 | F3 | M3 | L3 | 13 Oct 1999 |
4 | F4 | M4 | L4 | 14 Oct 1993 |
5 | F5 | M5 | L5 | 23 Apr 2001 |
6 | F6 | M6 | L6 | 24 Aug 2003 |
7 | F7 | M7 | L7 | 24 Dec 2005 |
8 | F8 | M8 | L8 | 25 Apr 2008 |
9 | F9 | M9 | L9 | 26 Aug 2010 |
10 | F10 | M10 | L10 | 26 Dec 2012 |
MProgramParticipant
ProgramCenterID | ParticipantID |
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
2 | 3 |
2 | 5 |
2 | 2 |
3 | 3 |
3 | 4 |
3 | 5 |
3 | 1 |
4 | 1 |
4 | 2 |
4 | 3 |
4 | 4 |
5 | 2 |
5 | 3 |
5 | 4 |
5 | 5 |
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.