Help creating Link Table

[chris1987]

Hi,

I've been reading about link tables and fact tables and I'm pretty sure I get the theory now, but applying this is confusing me! I've tried the following code but I get multiple $syn tables.

Please can someone help me understand where I'm going wrong (or correct my script for me?) I'm looking to have the "Star" schema with the MasterTable in the middle and each of the other tables connected to it.

Thanks

Chris

MasterCalendar:

Load * inline[

Date,WeekPeriod,Year,

26/03/2018,201815,2018

26/02/2018,201808,2018

26/01/2018,201804,2018

26/04/2018,201819,2018

];

OrderTable:

Load * inline[

O_Date,O_Item,O_Cust,O_Sales

26/03/2018,025522,650000,C Walsh

26/02/2018,023443,650001,R Johnson

26/01/2018,023444,650000,C Walsh

26/04/2018,023445,650002,R Johnson

];

InvoiceTable:

Load * inline[

I_Date,I_Item,I_Cust,I_Sales

26/03/2018,12345,650000,J Smith

26/02/2018,023443,650001,R Rabbit

26/01/2018,98765,650002,C Walsh

26/04/2018,023445,650003,R Johnson

];

QuoteTable:

Load * inline[

Q_Date,Q_Item,Q_Cust,Q_Sales

26/03/2018,67890,650002,J Smith

26/02/2018,30160,650001,R Rabbit

26/01/2018,023552,650000,C Walsh

26/04/2018,023445,650004,R Johnson

];

MasterTable:

Load Date resident MasterCalendar;

Join (MasterTable)

Load Q_Date AS Date,Q_Item,Q_Cust,Q_Sales Resident QuoteTable;

Join (MasterTable)

Load O_Date AS Date,O_Item,O_Cust,O_Sales Resident OrderTable;

Join (MasterTable)

Load I_Date AS Date,I_Item,I_Cust,I_Sales Resident InvoiceTable;

[boorgura]

Join in Qlik will actually add the columns/fields to the master table.

If you intend to use joins, as in your script, you will have to drop all the other tables. And you will end up with a flat single table.

If the goal is to achieve a star schema - do not use JOIN. Rename the key fields in all tables to be named the same.

For example, changing all the Dates to 'Date' - will result in a association across all table with Date field.

[juraj_misina]

Hi,

MasterCalendar:

Load * inline [

Date,WeekPeriod,Year,

26/03/2018,201815,2018

26/02/2018,201808,2018

26/01/2018,201804,2018

26/04/2018,201819,2018

];

OrderTable:

Load

    AutoNumber(O_Date&'|'&O_Item&'|'&O_Cust)    as %Key,

    *

inline [

O_Date,O_Item,O_Cust,O_Sales

26/03/2018,025522,650000,C Walsh

26/02/2018,023443,650001,R Johnson

26/01/2018,023444,650000,C Walsh

26/04/2018,023445,650002,R Johnson

];

InvoiceTable:

Load

    AutoNumber(I_Date&'|'&I_Item&'|'&I_Cust)    as %Key,

    *

inline [

I_Date,I_Item,I_Cust,I_Sales

26/03/2018,12345,650000,J Smith

26/02/2018,023443,650001,R Rabbit

26/01/2018,98765,650002,C Walsh

26/04/2018,023445,650003,R Johnson

];

QuoteTable:

Load

    AutoNumber(Q_Date&'|'&Q_Item&'|'&Q_Cust)    as %Key,

    *

inline [

Q_Date,Q_Item,Q_Cust,Q_Sales

26/03/2018,67890,650002,J Smith

26/02/2018,30160,650001,R Rabbit

26/01/2018,023552,650000,C Walsh

26/04/2018,023445,650004,R Johnson

];

LinkTable:

LOAD Distinct

%Key,

O_Date    as Date,

O_Item    as Item,

O_Cust    as Cust

Resident OrderTable;

Drop Fields O_Date, O_Item, O_Cust from OrderTable;

Concatenate(LinkTable)

LOAD Distinct

%Key,

I_Date    as Date,

I_Item    as Item,

I_Cust    as Cust

Resident InvoiceTable;

Drop Fields I_Date, I_Item, I_Cust from InvoiceTable;

Concatenate(LinkTable)

LOAD Distinct

%Key,

Q_Date    as Date,

Q_Item    as Item,

Q_Cust    as Cust

Resident QuoteTable;

Drop Fields Q_Date, Q_Item, Q_Cust from QuoteTable;

Hope this helps.

Juraj