Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
lostSquirrel112
Contributor II
Contributor II

Circular References - Data Model Issues

I have a data model that consists of a few tables...

Company Data (these are our customers)

Contact Data (these are individuals at the companies)

Sales Rep Data (this is user data on our sales reps)

Sales data (this is sales/transaction data)

Call data (this is outbound sales call data)

Email data (this is outbound sales email data)

 

A sample data model can be loaded like so...

 

CompanyData:
LOAD * INLINE [
companyID,companyName
A,BigCorp
B,MidCorp
C,SmallCorp
];

 

ContactData:
LOAD * INLINE [
contactID,companyID,contactName,salesRepID
1,A,Janet C.,998
2,B,Mike B.,999
3,C,Jim M.,997
];


SalesRepData:
LOAD * INLINE [
salesRepID,salesRepName
997,Ted
998,Bill
999,Bradley
];


SalesData:
LOAD * INLINE [
salesID,companyID,salesRepID
1,A,998
2,A,998
3,B,999
];

 

CallData:
LOAD * INLINE [
callID,contactID,salesRepID
1,1,998
2,2,998
3,3,998
];


EmailData:
LOAD * INLINE [
emailID,contactID,salesRepID
1,3,999
2,2,998
3,1,999
];

 

ContactToCompanyMapping:
Load
contactID,
companyID
Resident ContactData;
Drop Field companyID FROM ContactData;


CallToUserMapping:
Load
callID,
salesRepID
Resident CallData;
Drop Field salesRepID FROM CallData;

 

emailToUserMapping:
Load
emailID,
salesRepID
Resident EmailData;
Drop Field salesRepID FROM EmailData;

 

 

 

Each company can have a contact and each contact has a sales rep assigned to them as the account owner.

Company -> Contact -> SalesRep

I'm trying to track the sales efforts (calls and emails) of our sales reps, but the current data model is creating circular references. This is due to sales reps may make a call or send an email to a company contact and when the contact calls back a different sales rep answers the call and helps them place the order. So a sale is created on the sales table linked to that company but a different salesRep than the "account owner".

 

I'm getting the following data model when I try to load.

Screen Shot 2022-03-18 at 4.31.12 PM.png

 

I've created "mapping tables" to avoid synthetic keys but now I'm getting circular references. 

Each email or call can be linked to a contact from a sales rep. But the account owner may or may not be the person to make that call or even take the order. So I don't want to drop the contactID and SalesRepID fields, that's important data to know who is doing the sales activities and who are they reaching out to but it either creates synthetic keys or circular references.

Please help!

Labels (3)
0 Replies