Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sudhakar_budde
Contributor

One to many Relationships

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

1 Solution

Accepted Solutions

Re: One to many Relationships

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

4 Replies
Not applicable

Re: One to many Relationships

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

sudhakar_budde
Contributor

Re: One to many Relationships

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

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

Not applicable

Re: One to many Relationships

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 ?

Community Browser