Exercise - Solving Synthetic Key
· Go back to the Script Editor and locate the Shipment table.
· Make a comment of the field ProductID.
· Create a composite field of OrderID and LineNo using the "&" to concatenate the fields. Make sure to put a separator in between the fields as well. Name the key field OrderLineKey.
· Use the autonumber function to get a number instead of text for the key field. This is especially useful when working with large tables since numeric values takes up less memory than text values.
· Comment the fields OrderID and LineNo. This is necessary to avoid the synthetic key and have only one (OrderLineKey) connecting field between the Shipments and the OrderDetails tables.
· Create the same OrderLineKey in the OrderDetails table.
Why do you believe this synthetic key is a problem to be solved? It looks fine to me on the surface. Leaving it alone would make for a simpler and faster script and I suspect perform as well or marginally better than creating your own concatenated key table.
Synthetic keys are not always bad. People often think they are, perhaps because they often appear on accident if you have data model problems. But if this really is the right data model, and all you would do to "solve" the synthetic key is to create your own concatenated key, you might as well just leave the synthetic key alone. This synthetic key IS a concatenated key, essentially. It's just one built automatically by QlikView. So the synthetic key is probably the solution, not the problem.
Thanks for your comment! I heard that if the synthetic keys are there then performance issue will be there. That's the reason I am looking forward to remove the synthetic key.
Attached application is small as it is only part of the application. But when I added other parts into it, there was some performance issue.
Anyway as suggested I have concatenated those fields and there is synthetic key issue. Please look into the application and let me know whether i have done in the right way.
Also there are some other methods to avoid the synthetic key. Can you please explain me the same.
I got this error when I loaded the data with all the tables. What is the cause for it?
One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.
Synthetic keys.qvw 612.8 K
I'm trying to figure out your data, but I'm afraid I don't quite get it.
It looks like you have two different tables with some medical companies in them, and basic information about them, like their address. These are connected together by a "subarea code" and a "status". But I can't make any sense of the code or the status. So I don't know what the connection between the tables is supposed to be. Therefore, I can't tell you if they are connected correctly or not. Since you say you're having performance problems, I'm going to guess not.
I also can't say whether or not you should have a synthetic key. If it is appropriate to link these tables on "subarea code" and "status", then I think it's appropriate to have a synthetic key. If that is NOT appropriate, then you should remove the synthetic key.
But I can't tell you the right way to remove the synthetic key unless I know what the connection between the two tables SHOULD be.
As ONE example of how to remove a synthetic key, let's say that the relationship here is just something basic like "trips". Someone drove from one company to the other. We want to record that relationship.
If that were the case, you'd want ONE table for the companies, not two. Then you'd want a TRIP table that recorded the from/to information. One way would be to have "From Company" and "To Company" as fields, but I'd probably instead do this:
TripID, From/To, Company
1, From, A
1, To, B
That way, if you select company A, you can see ALL trips, whether they started or ended at company A. On the other hand, you couldn't search for all trips that started at companies A-F, and ended at companies C-H. That would required the two field approach. But that's all kind of beside the point. The idea is that unless I know what data modeling problem you're trying to solve, and perhaps even how you want the user to interact with your data, I have no hope of solving this for you.
As for synthetic keys themselves, I'll reiterate that I don't think they're a problem. In fact, I believe that most people get this wrong, which inspired me to write a large post on this very subject.