topic How to avoid loop between invoice and order? in QlikView

How to avoid loop between invoice and order?

hfkchristian — Thu, 18 Feb 2016 09:39:04 GMT

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.

Re: How to avoid loop between invoice and order?

petter — Thu, 18 Feb 2016 18:24:43 GMT

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.

Re: How to avoid loop between invoice and order?

hfkchristian — Fri, 19 Feb 2016 09:12:19 GMT

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?

Re: How to avoid loop between invoice and order?

petter — Fri, 19 Feb 2016 11:15:10 GMT

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.

Re: How to avoid loop between invoice and order?

hfkchristian — Fri, 19 Feb 2016 11:19:08 GMT

Thanks I will join the tables then.