I am new to QV and have a question about circular referencing:
I have the following simple structure of 6 tables which creates a loop:
Cards, Items, Orders, Order Lines, Invoices and Invoice Lines
I would like to have two sheets, or two parts of the same sheet wich will allow both card-wise and item-wise analysis. I would like to select the Card and see both the Orders and Invoices it has, as well as selecting an Item and see in which Orders and Invoices it appears.
I know I can do this by renaming either the Card or the Item field in one of the tables, but then I will not be able to answer both of the requirments above.
[bold]
This is a fairly simple one, but I need to understand this throughfully since the final data structure is much more complex and involves much more tables. What would be the right approach for it?
[/bold]
Cards:
SELECT CardCode, CardName FROM OCRD; Items:
SELECT ItemCode, ItemName FROM OITM; OrderHeaders:
SELECT CardCode, DocEntry AS OrderNum, DocDate AS OrderDate FROM ORDR; InvoiceHeaders:
SELECT CardCode, DocEntry AS InvoiceNum, DocDate AS InvoiceDate FROM OINV; OrderLines:
SELECT DocEntry AS OrderNum, ItemCode, Quantity AS OrderLineQty FROM RDR1; InvoiceLines:
SELECT DocEntry AS InvoiceNum, ItemCode, Quantity AS InvoiceLineQty FROM INV1;
I know that I can basically turn it into less tables and rename either the CardCode or the ItemCode field to avoid the loop, but I want to allow analysis by both of them.
With many years of experience in database design, I hope you understand when I tell you that there is no ONE generic pattern to solve all of your problems. There are a LOT of structures that you'll want to use, and it always depends on the situation. We could probably come up with a fairly-extensive SET of patterns that would serve most of your needs, but those patterns wouldn't be anything specific to QlikView. They'd likely be patterns that you're already familiar with.
So you're right, "it shouldn't be a big problem to understand the concept of how to design a complex structure in QV on my own based on an existing DB structure, given the fact I do have many years of expirience in database design." You shouldn't need a consultant, and a consultant could easily have less experience in database design than you do, and lead you astray. You just need to think your way through everything. QlikView can deal with a fair bit of denormalization just fine, so you lose that constraint for the most part. But in every case with all possible values for your data, it must be able to produce a self-consistent set of data to match any selections you make.
My general recommendation is to use a star or snowflake schema, but I don't specifically design with that in mind, that's just where my table structures tend to end up. When I first started, I was creating very normalized data structures in QlikView, more normalized than our actual databases (which have some performance compromises). It dealt with them just fine. I just eventually concluded that QlikView would be happier with certain kinds of denormalizations, generally putting a lot more information on one or two central fact tables than strictly belonged there from a data normalization perspective.
Given your extensive background, you might be best off trying to design your own solutions, and then if you're uncomfortable with what you've come up with, just run it by the forum and see if others can come up with better solutions.
As to your new example, if you post a QVW with some sample data like I did, I'll be happy to take a look. But I encourage you to try to figure it out yourself as well. What I would suggest is creating inline loads that mimic reading a few rows each from your actual databases. You can post that. You've already mentioned some kinds of analysis you'd like to support, but any further examples would be good as well. Ideal is "if I make these selections, I want a table that shows this exact information." From there, both of us can try to figure out how to get from your original data model to a perhaps new data model and from there to charts that will give you what you want.
I will be on a couple weeks vacation starting Thursday, so I can't guarantee I'll get to this soon, but others may be happy to help as well.
Well, first things first, and apologies if you already know all this, but someone else may not. When you make a selection, QlikView must come up with a consistent, matching set of data. This must ALWAYS be possible, and therefore must be guaranteed by your DATA MODEL, not just by your DATA. When you have a circular reference, it is at least possible to load in data that prevents QlikView from doing what it must do to work at all. Therefore, it gives you a warning, and "arbitrarily" breaks apart your data model to prevent this condition.
Here is an example of some data using your data model that would drive QlikView mad if it didn't arbitrarily break your data model apart:
What happens when you select order number OH? Order number OH1 gives you card code CC1, which gives you invoice number IN1, which gives you item code IC1, which gives you order number OH2, not OH1. QlikView is unable to establish a consistent set of data that matches your selection. This sort of data might be impossible in your source system, but it isn't being prevented by the data model itself, so QlikView plays it cautious.
One typical solution might be to concatenate your Invoices and Orders, and distinguish between the two by a type field.
Now what happens when you select order number OH1? Since it is in the same table as invoice IN1, your selection specifically exludes invoice IN1. You therefore never head down the chain that tries to choose order number OH2 instead. You get a consistent set of data, which is the one row from the Headers table, and no rows from the Lines table. Now, you might still have bad DATA, and you may want to address that. But you can choose internally-consistent sets from this bad data, and don't have a loop.
Now I can't say whether or not this is the RIGHT solution for your data. It would be a rather poor choice if, for instance, you wanted to see invoices associated with orders. And there are probably a lot of other ways of resolving the circular reference. But it's one possibility.
I can definetly concatenate both tables in this case to get what I need in this example
but
I am more in a process of getting some sort of generic pattern for solving those issues since I need to design a very large model that should cover all my system data. Not objects have the same structure - for instance, imagine that instead of invoices I want to show installbase information, which is a flat table with warranty dates and ItemCode field which represents the product and CardCode field which represents the customer to which this data belongs. Now, I would like to see all orders of a customer and all items he has warranty for (when selecting the customer), and on the other hand, I'd like to see all installbase items and all orders where a certain item is present (when selecting the item).
And again, this is a simplified version of the real structure, when both the Customer and the Item appear in dozens of tables..
If it is your first project, It might be better to involve a experienced QlikView consultant, and learn while working together.QlikView Projects generally get delayed significantly, if you do not have a experienced guy doing the design with you.
Sorry, there is no simple answer. Going through QlikView advanced training might help.
I am definetly considering it for the technical part which requires in depth knowledge of the QV functionality. However, I think it shouldn't be a big problem to understand the concept of how to design a complex structure in QV on my own based on an existing DB structure, given the fact I do have many years of expirience in database design..
With many years of experience in database design, I hope you understand when I tell you that there is no ONE generic pattern to solve all of your problems. There are a LOT of structures that you'll want to use, and it always depends on the situation. We could probably come up with a fairly-extensive SET of patterns that would serve most of your needs, but those patterns wouldn't be anything specific to QlikView. They'd likely be patterns that you're already familiar with.
So you're right, "it shouldn't be a big problem to understand the concept of how to design a complex structure in QV on my own based on an existing DB structure, given the fact I do have many years of expirience in database design." You shouldn't need a consultant, and a consultant could easily have less experience in database design than you do, and lead you astray. You just need to think your way through everything. QlikView can deal with a fair bit of denormalization just fine, so you lose that constraint for the most part. But in every case with all possible values for your data, it must be able to produce a self-consistent set of data to match any selections you make.
My general recommendation is to use a star or snowflake schema, but I don't specifically design with that in mind, that's just where my table structures tend to end up. When I first started, I was creating very normalized data structures in QlikView, more normalized than our actual databases (which have some performance compromises). It dealt with them just fine. I just eventually concluded that QlikView would be happier with certain kinds of denormalizations, generally putting a lot more information on one or two central fact tables than strictly belonged there from a data normalization perspective.
Given your extensive background, you might be best off trying to design your own solutions, and then if you're uncomfortable with what you've come up with, just run it by the forum and see if others can come up with better solutions.
As to your new example, if you post a QVW with some sample data like I did, I'll be happy to take a look. But I encourage you to try to figure it out yourself as well. What I would suggest is creating inline loads that mimic reading a few rows each from your actual databases. You can post that. You've already mentioned some kinds of analysis you'd like to support, but any further examples would be good as well. Ideal is "if I make these selections, I want a table that shows this exact information." From there, both of us can try to figure out how to get from your original data model to a perhaps new data model and from there to charts that will give you what you want.
I will be on a couple weeks vacation starting Thursday, so I can't guarantee I'll get to this soon, but others may be happy to help as well.
After a few more weeks of work with QV I've come to accept that this is just the way things are, and strangely, it's not bothering me anymore. So far I've managed to find one solution or another for any practical issue I had.
