Need help with Data Model

[jiwaniakbiar]

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?

[petter]

First of all doing any kind of aggregations on the key fields lead to ambiguous results. You can see this as Count(ParticipantID) gives 10 which is what you would expect whereas Count(ProgramCenterID) will give you 19 which is not intuitive.

So to have some control of what you are aggregating you would need a set of helper columns which you can use to aggregate correctly.

Add an extra field to each of the tables that are a non-key fields:

You load script would look like this:

[PROGRAM CENTER]:
LOAD
    RowNo() AS PC#,
    ProgramCenterID,
    Fees,
    Venue,
    Date
FROM [lib://DATA]
(html, utf8, embedded labels, table is @1);

[PARTICIPANT]:
LOAD
    RowNo() AS P#,
    ParticipantID,
    [First Name],
    [Middle Name],
    [Last Name],
    DOB
FROM [lib://DATA]
(html, utf8, embedded labels, table is @2);

[CENTER_PARTICIPANT]:
LOAD
    RowNo() AS C_P#,
    ProgramCenterID,
    ParticipantID
FROM [lib://DATA]
(html, utf8, embedded labels, table is @3);

The number of program participants that attend at least one program is:

Count( {<P#=P(PC#)>} P#)

Using the P() element function will make sure that there are any possible program centers associated with a participant.

Count( C_P# )

This is straightforward as this is a unique row id for the table CENTER_PARTICIPANT.

[jiwaniakbiar]

Thank You. I will try this and update