0 Replies Latest reply: Jul 31, 2017 11:10 AM by Sandra Pinto RSS

    Creating A Link Table

    Sandra Pinto

      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