I have two tables - Employees and Service Calls. In service calls, I have a field for Creating Employee, Closing Employee and Currently Handling Employee - so that each call has 3 links to the employees table. I would like to build a simple screen on which I can choose the Employee in the list of employees and then see in 3 separate tables the calls opened, closed, and currently being handled by him. So far the only option I see is to load the employees table once and load the calls table 3 times, renaming the relevant Employee field each time to create the link.
I am quite sure there is a better way to handle it - is there?
M4U wrote:On a more global scale though, assuming I have quite a complex database I want to analyze in QV, with around 100 tables linked among themselves (basically a small ERP app) this approach will lead to a certan mess. Instead of having clear entities just like in the real db I will endup having something totally different. I am sure I'm not the first one who needs something like that.. any tips, pointers?
Different requirements, different solutions. Tables in a business system can be very normalized, and may be designed primarily for the storage and processing of information. QlikView is primarily for the reporting of information, which is a far different thing. Even though it will work perfectly well with highly-normalized information, denormalization is often desirable. QlikView's compression often resolves the space requirements of denormalized data, and with no updates possible, your denormalized data can't get out of sync. My final tables in QlikView at best peripherally resemble my tables in the business system, even when I've designed both.
But you shouldn't end up with an unrecognizable mess, either. I would suggest star or snowflake schema most of the time, which behave very well, and seem like a fairly straightforward arrangement of data to me. I more often combine tables than split tables apart in QlikView, even if I do some of both. When my source data has tables that associate a code with its description and a couple other small pieces of data, I will typically just left join onto my main fact table. I only rarely have cases like yours, where you may be best off creating multiple duplicates of a table with different field names.
As for needing to concatenate multiple-column keys, this isn't generally necessary. QlikView will create a "synthetic key" for you. These seem to have a very bad reputation, as they seem to most typically appear when you've made a mistake in the data model. However, they CAN be a good thing. In the case of two tables that you want associated with each other by a multiple-column key, a synthetic key is exactly what you need. You CAN build your own "synthetic key" by autonumbering a concatenation of the fields, but in my experience it's at best unnecessary and at worst counterproductive.
I would suggest changing yout table for Service Calls, to one column to identify the original call ID, one column to break down your 3 fields, ie creating employee, closing employee and currently handling employee, and then a third column to identify the employee. You can then link this employee to your first employee table and easily produce tables/charts to show each call and call type assigned to your employees.
1) If you are ready to spilit your Service Calls table into three and each should have a unique ID field and first table should contain Creating Employee second table should contain Closing Employee and the third should contain Currently Handling Employee. In this way a single employee table can be referred by the above three tables.
2) Keep your existing Service Calls table but need to make three Employee table selection using alias name ( select * from employee CreatingEmployee, select * from employee ClosingEmployee and select * from employee CurrentlyHandlingEmployee ).
I'd probably use Damien's approach if it were my data, but with a third table connecting your employees to your service calls. Employee table would have all the information about the employee. Service Calls table would have all the information about the service call EXCEPT which employee(s) were involved. Your third table would have the three columns mentioned and three ROWS for every row on the Service Calls table. You can create this third table with a crosstable load from the Service Calls table, which will automatically create rows from columns.
A possible weakness of this approach is that you can't specifically look for calls that were, for instance, created by Bob and closed by Sally. If that's something you want to be able to do, then you're probably looking at splitting tables apart as suggested by gajapathy74. I've done it all of these ways in the past depending on need.
Thanks for the help, I think I'll go with partitioning the service calls table as suggested into 3 parts with ID and employee in each - this will do the job for this small case.
On a more global scale though, assuming I have quite a complex database I want to analyze in QV, with around 100 tables linked among themselves (basically a small ERP app) this approach will lead to a certan mess. Instead of having clear entities just like in the real db I will endup having something totally different.
I am sure I'm not the first one who needs something like that.. any tips, pointers?
If you have a database structure which contains correctly linked tables then you will have no problems in viewing this data in qlikview, we already use qlikview to analyse data structures that contain over 100 tables with no issues.
"Correctly linked" is a very relative term. It's an existing ERP app with many scenarios like the above, circular referencing, multi-column keys and other things which I couldn't find a "natural" method of handling in QV..
Not sure that Qlikview likes circular referencing but can certainly handle multi column keys, they just need to be concatenated in the relevant tables and named the same.
M4U wrote:On a more global scale though, assuming I have quite a complex database I want to analyze in QV, with around 100 tables linked among themselves (basically a small ERP app) this approach will lead to a certan mess. Instead of having clear entities just like in the real db I will endup having something totally different. I am sure I'm not the first one who needs something like that.. any tips, pointers?
Different requirements, different solutions. Tables in a business system can be very normalized, and may be designed primarily for the storage and processing of information. QlikView is primarily for the reporting of information, which is a far different thing. Even though it will work perfectly well with highly-normalized information, denormalization is often desirable. QlikView's compression often resolves the space requirements of denormalized data, and with no updates possible, your denormalized data can't get out of sync. My final tables in QlikView at best peripherally resemble my tables in the business system, even when I've designed both.
But you shouldn't end up with an unrecognizable mess, either. I would suggest star or snowflake schema most of the time, which behave very well, and seem like a fairly straightforward arrangement of data to me. I more often combine tables than split tables apart in QlikView, even if I do some of both. When my source data has tables that associate a code with its description and a couple other small pieces of data, I will typically just left join onto my main fact table. I only rarely have cases like yours, where you may be best off creating multiple duplicates of a table with different field names.
As for needing to concatenate multiple-column keys, this isn't generally necessary. QlikView will create a "synthetic key" for you. These seem to have a very bad reputation, as they seem to most typically appear when you've made a mistake in the data model. However, they CAN be a good thing. In the case of two tables that you want associated with each other by a multiple-column key, a synthetic key is exactly what you need. You CAN build your own "synthetic key" by autonumbering a concatenation of the fields, but in my experience it's at best unnecessary and at worst counterproductive.
