Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Creating A Link Table

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

0 Replies