Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 *;
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.
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.
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.
You need to check for circular loops.