Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Reuven12
Contributor II
Contributor II

Canonical Calendar for Budget and Sales

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? 

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

Reuven12
Contributor II
Contributor II
Author

Hey, 

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

 

marcus_sommer
MVP & Luminary
MVP & Luminary

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