Multiple Master Tables

[Anonymous]

I have 2 fact tables (Sales and AR) and 2 master tables (Calendar and Company).  The Sales and AR tables each have a date and a company code that need to be linked to the calendar and company tables.  No matter what I try I can't seem to get this to work without getting either a $syn error or a circular looping error.  Here is what I have right now.  Any suggestions?

SALES:
LOAD
[invoice date] as [invoice date],
[sales entity],
[amount],
[gl account]
FROM $(vPathNameSalesLayer)SalesLayer_sales.qvd(qvd);

AR:
LOAD
[ar entity] as [ar entity],
[customer number],
[trans date] as [trans date],
[trans code],
[trans amt],
[trans description]
FROM $(vPathNameARLayer)ARLayer_ar.qvd(qvd);

COMPANY_MASTER:
LOAD
[titan entity code],
[titan entity code] as [ar entity],
[titan entity code] as [sales entity],
[company code],
[company name]
FROM $(vPathNameLayer1)Epicor_Titan_CompanyMaster.qvd(qvd);

CALENDAR_MASTER:
LOAD
[invoice date] as [date],
[invoice date] as [trans date],
[invoice date] as [invoice date],
[acct month],
[acct year]
FROM $(vPathNameLayer1)MasterCalendarAll.qvd(qvd);

[Gysbert_Wassenaar]

Either load company_master twice, once for each role. Or use mapping tables and applymap to add the company field(s) or to replace the xx entity fields with the company name. The latter option is better if all you're really interested in is the company name. You do have to make sure you don't give it the same field name in both fact tables or you'll still have a loop. So use for example [company name ar] and [company name sales] instead of [company name].

Example:

MapSalesCo:

mapping load

[titan entity code],

[company name]

from $(vPathNameLayer1)Epicor_Titan_CompanyMaster.qvd(qvd);

SALES:

LOAD

[invoice date] as [invoice date],

[sales entity],

applymap('MapSalesCo',[sales entity]) as [company name sales];

[amount],

[gl account]

FROM $(vPathNameSalesLayer)SalesLayer_sales.qvd(qvd);

[Anonymous]

I think that would work but wouldn’t solve my original issue. I would like to have a filter of company name so the user can select a company name and the data from both tables are displayed. If the names of the company name field are different in each table then this will not work.

[swuehl]

Have you considered concatenating your two fact tables, having a common company and date field?