How to avoid loop between invoice and order?

[hfkchristian]

Hi all

I'm trying to make a data model of invoices and orders. I have three tables like this:

Order	Invoice header	Invoice line
Order_number Place Order_amount Order_date	Invoice_number Order_mumber Invoice_date	Invoice_number Place Invoice_amount

When I run the script, QlikView warns me about a loop created by Order_number and Place, and the Invoice line table becomes loosely connected.

Does anyone have an idea about how I could design this to avoid the loosely connected table? I want the user to be able to click on an order number and see connected orders and invoices (not all invoices have order numbers). At the same time, it should be possible to click on a place, and see everything ordered and invoiced at that place.

[petter]

Order:
LOAD
     Order_number,
     Place AS Order_place,
     Amount AS Order_amount,
     Order_date
......;

Invoice_header:
LOAD
     Invoice_number,
     Order_number,
     Invoice_date
.......;

Invoice_line:
LOAD
     Invoice_number,
     Place AS Invoice_line_place,
     Amount AS Line_amount
.....;

This will not falsely connect an invoice line amount with the order amount, not connect order place with invoice line place. The trick is to make each field more specific as to what they are representing independently from the table they are belonging to. So you keep the name for the two Order_number since they are really one and the same thing and should be used to connect Order and Invoice_header. Thus making it a key field to keep a correct association. The same goes for Invoice_number.

[hfkchristian]

I get rid of the loop if I join the Invoice_header and Invoice_line tables into a single Invoice table. But I know joins should be avoided in QlikView. Are there any special disadvanges about joining these tables like this?

[petter · Accepted solution]

In this particular case I would say it is good to join the tables. It is not always good to join tables. It is a balance between performance, the nature of the join, memory implications and clarity of the data model.

A join is performed in the Load Script and will thus not have performance implications for users when using the application. Not having an association between two tables and having them joined into one table will in many cases perform better. But again this depends on the number of fields in each table, the cardinality between them and also the size of each field in the tables.

As a general advice it is important to learn how to avoid loops and synthetic keys and understand it well if you want to create good data models.

[hfkchristian]

Thanks I will join the tables then.