Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
avinashelite

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.

View solution in original post

3 Replies
avinashelite

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
Specialist
Specialist

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
Master III
Master III

You need to check for circular loops.