topic Help with Data Model in App Development

Help with Data Model

jiwaniakbiar — Sun, 30 Sep 2018 05:48:23 GMT

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?

Re: Help with Data Model

rubenmarin — Sun, 30 Sep 2018 17:44:14 GMT

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

Re: Help with Data Model

jiwaniakbiar — Tue, 02 Oct 2018 08:09:57 GMT

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

Re: Help with Data Model

rubenmarin — Tue, 02 Oct 2018 11:39:10 GMT

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.