Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Stock date and sales date in data model

Hi, I have some tables: sales, stock, purchases and products. In each of them (wo products of corse) I have transaction date (or stock date). How to combine these tables with the calendar? When I create three separate keys in the calendar, a loop is created in the data model 😞  Thanks.example_dm.JPG

1 Solution

Accepted Solutions
Creator II
Creator II

Hi,

Create a Bridge date table/fact table  which will connect with the Master Calendar.

Example:

Link_Table_Temp:
Load
Distinct
%key_cloud ,
Date("Cloud_Date") AS Date,
Actual_DeptCode AS Department_code,
Actual_Department AS [Cloud Department],
Actual_Workload AS Workload,
Actual_Vendor AS Cloud_Vendor,
Actual_Account as account


Resident table1;

Concatenate (Link_Table_Temp)

Load
Distinct
%key_Forcloud 
Date(ForecAStDate) AS Date,
ForecASt_DeptCode AS Department_code,
ForecASt_Department AS [Cloud Department],

Resident table2

Concatenate (Link_Table_Temp)

Load
Distinct
[%key_hyperion]  ,
Date(perioddate) AS Date,
department AS Department_code,
account

Resident table3

Concatenate (Link_Table_Temp)

Load Distinct
[%key_expense]  ,
Expense_Vendor AS [Vendor Code],
GLACCOUNT as account,
Date(EXP_CLOSEDATE) AS Date,
EXP_GLDEPARTMENT AS Department_code

Resident table2;

 

now Date key should link with Master Calendar 

 

View solution in original post

1 Reply
Creator II
Creator II

Hi,

Create a Bridge date table/fact table  which will connect with the Master Calendar.

Example:

Link_Table_Temp:
Load
Distinct
%key_cloud ,
Date("Cloud_Date") AS Date,
Actual_DeptCode AS Department_code,
Actual_Department AS [Cloud Department],
Actual_Workload AS Workload,
Actual_Vendor AS Cloud_Vendor,
Actual_Account as account


Resident table1;

Concatenate (Link_Table_Temp)

Load
Distinct
%key_Forcloud 
Date(ForecAStDate) AS Date,
ForecASt_DeptCode AS Department_code,
ForecASt_Department AS [Cloud Department],

Resident table2

Concatenate (Link_Table_Temp)

Load
Distinct
[%key_hyperion]  ,
Date(perioddate) AS Date,
department AS Department_code,
account

Resident table3

Concatenate (Link_Table_Temp)

Load Distinct
[%key_expense]  ,
Expense_Vendor AS [Vendor Code],
GLACCOUNT as account,
Date(EXP_CLOSEDATE) AS Date,
EXP_GLDEPARTMENT AS Department_code

Resident table2;

 

now Date key should link with Master Calendar 

 

View solution in original post