More than one common Tables for two fact tables.

[#1]

i have an interesting situation where I have Sales and purchases connected with the Item_Master table. I also need to have common calendar apart from individual calendar of sales and purchases.

I am finding it very difficult to normalize into normal star schema to avoid a loop and synthetic keys.

Need expert advise how to resolve and normalize this diamond loop structure.

[#2]

Hi,

You can concatenate both of the fact tables together.

Add a TrxType field to identify whether it's a Sales or a Purchase transaction.

In this way you can have same Item and Date fields used for both types.

example:

Transactions:

load 'Sales' as TrxType,

ItemID,

Date,

...

from SalesTable;

concatenate(Transactions)

load 'Purchase' as TrxType,

ItemID,

Date

...

from PurchaseTable;

Hope this helps.

[maxgro]

usually concatenate or link table

you can find a lot of interesting answer (see the first ones) here

http://community.qlik.com/search.jspa?q=concatenate+link+table

concatenate is easiest, just follow jp_bakcache answer

[#4]

Thanks. I don't prefer to concatenate the tables as i looses the semantic of two logical entities in the business just to correct the data model, Moreover there are plenty of related tables connected to each which then need to be adjusted like  invoice, purchase order, payments, payable etc and that will muddle the entire data model and its usage in the application.

So finally I decided to use a floating master calender and use the set analysis parameters to apply the filter selected on that floating calender.

So far it worked without any issue.

[#5]

As we have a similar solution in our company I'd recommend you to concat both tables. This really simplifies further adpations.

But of course there are other possible approaches too.

Use two different product keys in your Product table, one linked to sales and one to purchases. As for the calendar part - dont link the master calendar to anything, but take use of If(date >= xxx) or better of a set analysis expression within your formulas.

Depending of the amount of product IDs and dates you could also create a link table containing all possible combinations of of product ids and dates and link this to sales, purchases, product table and master calendar

/e: aah didnt see your reply, you already solved it

[#6]

No, Still working. Trying to concat all the tables and now dealing with some phantom ids in the data.

[#7]

If you would rather keep the two tables separate then the linking table is your best bet.Your linking table should have a link to your sales table (i.e. Sales ID), purchases table(i.e. Purchase ID), Master_item table and calendar.

If you need any help with this you could post your qvw here.