topic Re: Associative model question in QlikView

Associative model question

Tue, 28 Oct 2014 16:25:08 GMT

Hello,

The more I use Qlikview, the more I'm unsure about what to do sometimes...

I have an application with a sales order table and a sales invoice table, which I concatenated into a fact table like this:

Sales Order	Sales Invoice	Order Value	Invoice Value	Origin
AAA		100		ORDERS
AAA	1234		100	INVOICES

Of course, there are dimension tables attached to the fact table.

Now, I was asked to make a report where I show the invoice value per order reason. I already have this field is in the order table, and not on the invoice table.

Can I make this with my current model, or should I change it and have a fact table like this, where I retrieve the order reason and put it into my fact table?:

Sales Order	Sales Invoice	Order Value	Invoice Value	Origin	OrderReason
AAA		100		ORDERS	F11
AAA	1234		100	INVOICES	F11

Thanks

Re: Associative model question

alexandros17 — Tue, 28 Oct 2014 16:28:06 GMT

The question is:

how invoices and orders are linked togheter? Probably you have order number into the invoice table so, if this is the case, I should associate table, concatenation is not the better choise for this situation because the difference of meanings of fields from the 2 tables.

Verify and let me know

Re: Associative model question

Tue, 28 Oct 2014 16:36:38 GMT

Ciao,

I had to concatenate the two tables into one fact table, because of a calendar request at the beginning of the application. The two tables have one field in common: the sales order number (AAA in my exemple above).

I'm sorry, I did not understand your comment about the meaning of fields.

Re: Associative model question

alexandros17 — Tue, 28 Oct 2014 16:39:14 GMT

So tables are naturally associated, there is no need of concatenation, let's find another way for date field, tell me about calendar, what is the problem?

Re: Associative model question

albertovarela — Tue, 28 Oct 2014 16:57:46 GMT

Have you considered to join some of the order attributes to the invoice table before concatenating the two tables?

Re: Associative model question

Tue, 28 Oct 2014 17:50:17 GMT

The request that led me to concatenate the two tables was to get a report like this : Date - Invoiced Value - Ordered Value.

That is why I concatenated the two tables and have a %Date_Key field in the fact table with the order date and the invoice date.

Re: Associative model question

Tue, 28 Oct 2014 17:52:29 GMT

Yes, I thought about it, but before I did that, I thought I would ask here what would be the best way to solve this problem. I know it would work fine, but I was wondering if there was any more elegant way.

Re: Associative model question

alexandros17 — Tue, 28 Oct 2014 21:46:32 GMT

This is not the best way because order date and invoice date must belong to 2 different calendar.

There must be a calendar for order and one for invoice, the tables must be associated by order no.

What is the meaning of concatenating date? if you select a date you will see orders and invoiced done in that date but is it really this the objective?

Re: Associative model question

Wed, 29 Oct 2014 07:52:28 GMT

Yes, it is the objective. They did not want two different calendars, but have a view of the activity per day. It is the only workaround about which I could think.

Re: Associative model question

alexandros17 — Wed, 29 Oct 2014 08:13:12 GMT

At this point you have to associate tables with the fields, date, the only thing you have to do is assigning the same name (with aliases) to the fields date in both tables

Re: Associative model question

Fri, 14 Nov 2014 16:42:57 GMT

I'm sorry that it took me so long to answer, but I think I did not understand your suggestion, because what I understood would create a loop.

Let's say that you have two tables: orders and invoices, with the following fields

- Orders

OrderDate
OrderNumber
OrderValue

- Invoices

InvoiceDate
InvoiceNumber
OrderNumber
InvoiceValue

How would you link them in your example so that I can show the Sum(OrderValue) and the Sum(InvoiceValue) on the same day?