How to deal with multiple fact tables

[#1]

I am new to Qlikview and running into what I guess is a quite common issue. I have searched the forums, but so far I haven't really figure out how to solve my issue. Here it is:

I have two fact tables:

• Table ordercosts: Orderline, ProductItem, CostID, Orderprocessingcosts
• Table productioncosts: ProductItem, CostID, Productioncosts

I use the CostID field to be able to analyse different types of costs included in the cost figures (I.e. labor costs, depreciation, energy costs, etc.). Both tables contain about 50.000 lines.

What should I do to prevent circular references? I could probably concatenate the two tables, but than transparency of the underlying business activities (order processing and production) is completely lost. Is there a more elegant way?

Thanks for helping me out.

[disqr_rm]

Best way to create a Link Table. What you can do is create a concatenated key with ProductItem, CostID and generate a link table.

Your tables would have:

1. ordercosts: Orderline, LinkTabKey, Orderprocessingcosts

2. productioncosts: LinkTabKey, Productioncosts

3. LinkTab: LinkTabKey, ProductItem, CostID

Try first and if need further help, just post a sample data file here.

[#3]

You can keep join the two tables on the productitem field and rename CosID in the first table to OrderCostId and CostId of the second table as ProductionCostId.

That will keep the two cost types separated but will avoid the circular reference.

Ex:

TableCost:

load OrderLine,

ProductItem,

CostId as OrderCostId,

OrderProcessingCosts

from odercosts;

keep join(TableCost)

load ProductItem,

CostId as ProductionCostID,

ProductionCosts

from productioncosts

Hope that helps.

[Anonymous]

Marc,
You certainly can follow suggestions from Rakesh and Philippe, but I don't see anything wrong with concatenation either. If you wish, you can add flags for processing and production, or keep dupicated IDs. Actually in the latter case it will be pretty much the Philippe's version.

[#5]

Hi All,

Thanks for your suggestions. I guess I'll just concatenate the tables. Maybe not the most elegant solution (i.e. business model not reflected in teable schema), but the other solutions seem a lot more complicated.

Thanks.