3 Replies Latest reply: Jul 14, 2014 7:14 AM by sujeet singh RSS

    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 *;