Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Issue with Association of tables

I have following 3 tables:

1. MProgram

//List of program programs

CREATE TABLE [dbo].[MProgram](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [ProgramCode] [int] NOT NULL,

    [CentreCode] [int] NULL,

    [Activity] [varchar](100) NULL,

    [AgeGroup] [varchar](50) NULL,

    [Venue] [varchar](50) NULL,

    [Fees] [int] NULL

2. MParticipant

//List of program participants

CREATE TABLE [dbo].[MParticipant](

    [ParticipantID] [int] IDENTITY(1,1) NOT NULL,

    [FirstName] [varchar](20) NULL,

    [MiddleName] [varchar](20) NULL,

    [LastName] [varchar](20) NULL,

    [Telephone] [varchar](20) NULL,

    [Mobile] [varchar](20) NULL,

    [Email] [varchar](30) NULL,

    [Gender] [varchar](2) NULL,

    [DateOfBirth] [datetime] NULL,

    [Remarks] [varchar](100) NULL,

3. MProgramParticipant

//Mapping of program participants to programs

CREATE TABLE [dbo].[MProgramParticipant](

    [ProgramCenterId] [int] NULL,

    [ParticipantID] [int] NULL

MProgram.ID maps to MProgramParticipant.ProgramCenterID

MParticipant.ParticipantID maps to MProgramParticipant.ParticipantID

MProgram has 2242 entries

MParticipant has 23032 entries

MProgramParticipant has 24603 entries. However, because it is mapping Program to participant id, it has multiple entries for both Program and Participant because each program is attended by multiple participants

I removed the duplicates and found that it has entries for 848 Unique Programs and 5084 Participants. I want to display this count on my dashboard as a metrics saying out of 23032 participants, only 5084 were covered as part of 848 programs.

Now, when i am loading this to Qlik Sense, MProgramParticipant.ParticipantID is associating with MParticipant.ParticipantID. Association name is ParticipantID.

When I am trying to display Count(distinct [ParticipantID]) it always shows the merge count of Participants 23032. Any suggestions?

0 Replies