Creating A Link Table

[Anonymous]

Hi,

I have 2 Fact Tables and I want to create a common Filed ( this item is actually in both of the Fact Tables).

Let me be a bit more specific:

First Fact Table is the Labor Expenses (from the Labor Cost Report)

Second Fact table is the P&L, which among other expenses includes labor expenses.

Both tables have A Division Code Number (this is a Product Division). There are 2 Such Division Codes: 121 and 122.

What I want to do is cread a link table for the 2 DIVISIONS, so that both fact tables relate to this Link table.

(that way, for example, in the UI, the user should be able to select 1 of the 2 Divisions and the Aggregated data should show only the selected data for both the LABOR Amounts and the P&L Amounts.

Can anyone suggest the best way to create the link table script?

Here is the script of both fact tables:

LABOR:

LOAD

    EmployeeID,

    EmployeeName,

    Department,   

    Date("Date") as LaborDate,     

    Account,

    Cost ,

    '121' AS DivsionLabor

FROM [lib://LaborData/LaborDataC.xlsx]

(ooxml, embedded labels, header is 2 lines, table is LABOR121);

Concatenate(LABOR)

LOAD

    EmployeeID,

    EmployeeName,

    Department,   

    Date("Date") as LaborDate,     

    Account ,   

     Cost,

     '122' AS DivsionLabor

FROM [lib://LaborData/LaborDataC.xlsx]

(ooxml, embedded labels, header is 2 lines, table is LABOR122);

// ========================================================

PL:

LOAD

    "Div" as DivsionPL,

    AccountID,

    Amount ,

    date(PLDate) as PLDate,

   AccountDesc

FROM [lib://LaborData/LaborDataC.xlsx]

(ooxml, embedded labels, header is 1 lines, table is PL)

WHERE "Div" = '121';

Concatenate(PL)

LOAD

    "Div" as DivisionPL,

    AccountID,

    Amount ,

    date(PLDate) as PLDate,

    AccountDesc

FROM [lib://LaborData/LaborDataC.xlsx]

(ooxml, embedded labels, header is 1 lines, table is PL)

WHERE "Div" = '122';

Many thanks