topic Re: Help creating Link Table in QlikView

Help creating Link Table

chris1987 — Wed, 28 Mar 2018 19:19:10 GMT

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;

Re: Help creating Link Table

boorgura — Wed, 28 Mar 2018 20:21:21 GMT

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.

Re: Help creating Link Table

juraj_misina — Wed, 28 Mar 2018 22:24:00 GMT

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