topic Re: Many-to-many mapping problem in QlikView

Many-to-many mapping problem

Mon, 14 Jul 2014 10:36:24 GMT

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

Re: Many-to-many mapping problem

avinashelite — Mon, 14 Jul 2014 10:45:32 GMT

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.

Re: Many-to-many mapping problem

maleksafa — Mon, 14 Jul 2014 11:10:51 GMT

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.

Re: Many-to-many mapping problem

sujeetsingh — Mon, 14 Jul 2014 11:14:49 GMT

You need to check for circular loops.