Associative model question

[#1]

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

[alexandros17]

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

[#3]

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.

[alexandros17]

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?

[albertovarela]

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

[#6]

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.

[#7]

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.

[alexandros17]

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?

[#9]

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.

[alexandros17]

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