topic Re: One to many Relationships in QlikView

One to many Relationships

sudhakar_budde — Fri, 14 Aug 2015 14:05:23 GMT

Hi,

I have table Client (where Client_type in (Clients, Agents and Contacts)). Primary Key is CL_ID.

I have another table WorkOrder with Cleint_ID, Agency_ID and Contact_ID. All these IDs join on Clients.CL_ID.

So I have derived Clients (CL_ID as %_CLIENT), Agency (%_AGENCY) and Contacts (%_CONTACT) from Client table and Joined with their respective Cleint_ID(%_CLIENT), Agency_ID (%_AGENCY) and Contact_ID (%_CONTACT) in the WorkOrder table.

Please see attached table view.

My question is : is this approach correct? What is the best way to reduce these tables instead of deriving more? How can I resolve this one-to-many relationship? Please help me.

Thanks in advance

Re: One to many Relationships

Fri, 14 Aug 2015 14:15:43 GMT

Instead making 3 client dimension tables , create one Client table with concatenate other client types like below.

Clients:

Load

'Clients' as %Client_Type // this field differentiate type of client

From Clients.qvd (qvd) ;

concatenate (Clients)

Load

'Agents' as %Client_Type // this field differentiate type of client

From Agents.qvd (qvd) ;

concatenate (Clients)

Load

'Contacts' as %Client_Type // this field differentiate type of client

From Contacts.qvd (qvd) ;

Re: One to many Relationships

sudhakar_budde — Fri, 14 Aug 2015 14:21:50 GMT

Hi Dathu,

Thanks for your reply.

But I have 3 keys Cleint_ID(%_CLIENT), Agency_ID (%_AGENCY) and Contact_ID (%_CONTACT) in the WorkOrder table. So, how can I join? Please see my attachment.

Re: One to many Relationships

Peter_Cammaert — Fri, 14 Aug 2015 14:28:09 GMT

You did a nice job of creating a star schema out of a compact database representation (all contacts of whatever type in a single table) And if it works, then it's correct.

A subsequent question you can ask yourself: is this an optimal solution? Is performance hampered by the extra hops and should you merge the fields you really need into the workorder table and throw away the others (throwing away what you don't really need is always a good thing)? Is there a massive amount of data present in the document, or is size rather limited and are we optimizing for fun while getting no real improvements...

A good read by HIC: A Myth about the Number of Hops

Another one by Miguel: The Importance Of Being Distinct

And another more philosophical post by HIC about what to do when: Data Modelling: Clarity vs. Speed

Peter

Re: One to many Relationships

Fri, 14 Aug 2015 15:43:11 GMT

You can rename the field names make into one common field between Fact & Dimension. I believe you have split your Fact table as well. Can you please post sample data with small no if rows ?