Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
evgeniystuchalk
Partner - Creator II
Partner - Creator II

Need help with Data Model

Hello! I'm building data model for CRM. I have following entities:

Lead

Contact

Task

Note

Each Lead connected to Contact

Tasks and notes can be connected to Lead or Contact

All connections works via id-fields.

When i'm loading data in 'as is' structure, i'm gettig SynKey (as espected ). Model looks like this:

742e1deec3.jpg

In attempt to avoid SynKeys, i created additional id fields, to follow the rule: "All tables connected with only one fields".

Tasks and Notes splitted in four tables instead two, for Leads and Contacts. SynKeys gone. Now model looks like this:

f92ec802f6.jpg

And finally, the question All of this tables contains another common fields. For example, Resposible-worker. Each deal, contact, task or note can have their own responsible worker (all from single list of workers). if i Qualify field Resposible-worker, i can't use it like single dimension for filtering. But i really need that What can i do with data model to reach that?

5 Replies
martinpohl
Partner - Master
Partner - Master

Hello,

I would concatenate Leads to Nodes (or in other order).

Example:

Leads_Notes:

sql select * from Notes;

conactenate (Leads_Notes) load *;

sql select * from Leads;

sql select * from Contacs;

sql select * from Task;

Like in your data model, lead_ID and Contact ID should have same fieldname

Regards

evgeniystuchalk
Partner - Creator II
Partner - Creator II
Author

You mean, I need to join all my tables in the one, for all entities?

Kushal_Chawda

You can comment the ContactID from Notes & Task table as you have this already in Lead table. Try to connect all table on LeadID

reddy-s
Master II
Master II

Hi,

You can concatenate the field in the sync table to form a unique key. Use this unique key to link your tables, this will remove the existing synthetic key.

Thanks,

Sangram.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Check this links for more help.

http://www.analyticsvidhya.com/blog/2014/11/synthetic-keys-qlikview-simplified/

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!