Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Many-to-many mapping problem

I have two tables; Contacts and Activities which have a many-to-many relationship. The many-to-many relationship is defined by the ActivityContactMapping table.

However the results I am getting are only showing one activity per contact.

As you can see below there is a loop created due to the Activity.ContactId field being present in other tables as well, which i think may be causing the issue. What am I doing wrong?

My script is below

mapping issue.png

QUALIFY *;

UNQUALIFY OwnerId;

UNQUALIFY Activity.ContactId;

UNQUALIFY ActivityId;

UNQUALIFY Activity.CalendarDate;

Activities:

LOAD Id AS ActivityId,

    WhoId AS Activity.ContactId,

    Subject,

    ActivityDate AS Activity.CalendarDate,

    OwnerId,

    AccountId,

    number_of_individual_activities__c,

    Activity_type__c;

SQL SELECT *

FROM Task

WHERE Type NOT IN ('Email','Call','RM/ Bloomberg Chat','Minor communication') AND Status = 'Completed';

UNQUALIFY *;

QUALIFY *;

UNQUALIFY ActivityId;

UNQUALIFY Activity.ContactId;

ActivityContactMapping:

LOAD Id AS MappingId,

    RelationId AS Activity.ContactId,

    TaskId AS ActivityId;

SQL SELECT *

FROM TaskRelation;

UNQUALIFY *;

QUALIFY *;

UNQUALIFY Activity.ContactRecordTypeId;

UNQUALIFY Activity.AccountId;

UNQUALIFY Activity.ContactId;

UNQUALIFY Activity.Department_Role__c;

UNQUALIFY Activity.Business__c;

Contacts:

LOAD Id as Activity.ContactId,

    AccountId AS Activity.AccountId,

    Name,

    RecordTypeId AS Activity.ContactRecordTypeId,

    CreatedDate,

    Department_Role__c AS Activity.Department_Role__c,

    Business__c AS Activity.Business__c,

    Asset_Class__c,

    Inactive_Contact__c,

    Fusion_user__c,

    Region__c;

SQL SELECT *

FROM Contact

WHERE RecordTypeId NOT IN ('012w000000063XQ','012w000000063qu');

UNQUALIFY *;

1 Solution

Accepted Solutions

Re: Many-to-many mapping problem

Hi Alex,

As per knowledge in qlikview, we need to follow one to one relationship between the table which is the best practice we follow in most of the projects. In your case the tables are  connected using two key that's why its forming  the circular loop.In your case connect the  Contact and Activities table directly using one key without the use of mapping table(in this case not needed) if u want to connect the other key as well use a linking table.

3 Replies

Re: Many-to-many mapping problem

Hi Alex,

As per knowledge in qlikview, we need to follow one to one relationship between the table which is the best practice we follow in most of the projects. In your case the tables are  connected using two key that's why its forming  the circular loop.In your case connect the  Contact and Activities table directly using one key without the use of mapping table(in this case not needed) if u want to connect the other key as well use a linking table.

maleksafa
Valued Contributor

Re: Many-to-many mapping problem

this will create a circular reference in your schema, you need to create a link table, or create a complex key in the activities table which makes it one to one relationship.

sujeetsingh
Honored Contributor III

Re: Many-to-many mapping problem

You need to check for circular loops.

Community Browser