I am new to Object Modeling and have an issue i need advice on:
I have a list of Contacts in my object model stored in a Contact Table (ContactId, Name, Birthday).
I want to use a reference to the contactId in many other areas of the object model
- I have a table that holds Accounts (AccountId, Name)
- I have table that holds Assets (AssetId, Name, Value)
- Accounts have Shareholders that are Contacts(ContactId)
- Assets are by owned by one or many Accounts (AccountId) or one or many Contacts (ContactId)
- Assets are managed one Management Company
- A Management Company can be managed by one or many Contacts (ContactId)
In my QLik Dashboard, I ultimately want to be able to consolidate when i select a contact , what assets they own directly or via one or many accounts and what they manage via a Management company.
I am in the process of drawing this in Excel and am here:
- I created a contact Table (ContactID, Contact Name, Birthday)
- I created an Asset Table (AssetID, Asset Name, Value, MgtCoID)
- I created an Account Table (AccountID, Account Name) and an AccountAssetOwnership Mapping Table (AccountID, AssetID, Ownership%) . In order to reflect that individuals can own an asset, I created "individual" accounts for each individual. There can be more than one account co-owning the same asset.
- I created an AccountContactMapping Table (ContactID, AccountID). A contact can own multiple Accounts (eg their own indivisual account and they sharehold of a company account)
With this i can get to what every contact owns directly or via an account.
How do i add my management company manager link ? I am getting stuck.
- I created a table to store all management companies (MgtCoID, Management Company Name)
- If i add a MgtCompany Manager Mapping Table (MgtCoID, ContactID) then I create a circular reference
- Should I add the MgtCo as accounts in my Accounts Table and create a "function" column in the AccountAsset Mapping Table with two types "Owns" or "Manages". But then how do i add the link to say that Contacts manage a MgtCompany?
Not sure but what if you join your management company table with the contact table, I mean keep common table for contact ID and related management company ID. I am trying to suggest that you associate your contacts to accounts and account to assets through one way link. But do you have MgmtCoIDs without any contact ID? in that case you may need to create dummy ID before joining the Mgmt company info in the contact tables.
Can you add the ContactID to the mgmt companies table and forego the link table with ContactID?
I can create a sample as well for you. If I don't see some of our experts jump on this, I can assist after my webinar.
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.
Thank you all for your advice. After some more thinking this is how I have for now moved along.
As a contact can play the role of MgtCo manager, direct owner and account shareholder at the same time and if they do I want to see the whole tree of sub objects they have an influence on, i have for now :
- merged all contacts (mgrs, direct owners and shareholders) into one contact table
- merged all accounts and mgt companies into one table and added a field that tells
me what type of account they are (Mgt Co or Asset Owner)
- created a contact to account mapping table where I can state that a contactID is a shareholder in account’ X, a manager in account Y. The mapping table has a contact ID, a an account ID and a relationship type.
- that account table is linked to assets As per above
This way I have no circular reference. however if a contact plays both the role of owner and manager of an asset then I have to make sure I am not double counting the value of the asset in my sums.
Watching your videos and reading up about circular references , I came to the conclusion that in fact my contactId did all represent the same thing but that their relationship to assets were multiple and of many types. I hope this is the right way to go.
Hi Helene - yes, this seems correct. Happy we could assist, please let us know how you do and if can help you further. That's the beauty of Qlik's associative technology - you really don't have to be concerned with specific data model referential integrity in most cases. It just works by simply associating the columns. If there is anything I can do to further assist, please let me know.
You may also want to take a look at this video as well, just to gather additional data prep knowledge.