Canonical Calendar for Budget and Sales

[Reuven12]

Hello, 

I'm new in Qlik sense and I Cant resolve an issue of 2 differents dates.

I have two tables: one for budget  one for sales and I can't figure out how to connect them to master without create a loop.

Sales:
Load
CustomerID,
[Sales date] ,
OrderID,
Quantity,
Amount  Sales,
ProductID,
IF(CANCEL_FLAG='Y', 'Y','N') as CANCEL_FLAG
Resident Sales

Budget:

Load

[Budget date],

CategoryID,
Daily_Budget 
Resident Calendar;
drop table Calendar;

I want to create a bridge table that will connect both of the tables and to the master calendar but I can't understand how.

I tried something like that:

Bridge:
Load
[Sales date] ,
[Sales date] , as CanonicalDate,
'Order' as DateType
Resident Sales;

Load
[Budget date],
[Budget date] as CanonicalDate,
'Budget' as DateType
Resident Budget;

But it doesn't work.

how can I do it correctly? 

[marcus_sommer]

My suggestion is to forget the link-table approach and just to concatenate both tables.

- Marcus

[Reuven12]

Hey, 

I thought so but I still want to check if there is another way.

[marcus_sommer · Accepted solution]

You need to include the canonical date within sales and budget tables and removing the sales/budget dates from the bridge table.

But this addressed only the date-part and not the other dimensions - products, customer and so on will probably have also dimension-tables. This means if you really want to go with a link-table approach you need to include them all to the bridge by combining all ID's into a key.

If there are no special requirements you don't need a link-table datamodel - IMO it should be always the last option which should be considered to build a datamodel.

- Marcus