Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
mustafaelryah
Creator
Creator

Connect Multiple dates in defferent tables to one master calender

if I have accounting transaction table like
transaction_date, credit ,debit, cost_center, account
The master calendar is connected via transaction_date
now I have sales data (sales_date, item, cost center, amount) and I want to connect sales data to a master calendar and also I want to connect the sales data with a transaction via cost_center
but I can't make it with key (sales_date & Cost center) because sometimes there are sales without a transaction on the same date. So please help me how to connect both to one master calendar without affecting each other.

Labels (1)
3 Replies
rubenmarin

Hi, there are usually two solutions for this:

- Concatenate sales and transaction tables, so you end with only one fact table, wichmakes easier other reltaions with dimesnion tables avoiding circular references.

- Add keys from sales to tansaction table, so transaction has all the needed keys to allow sale to reach the calendar, something like:

// Load all keys loaded in transactions
chkKey:
LOAD distinct Key as chkKey resident Transactions;

// Add keys from Sales not already loaded in transactions
Concatenate (Transactions)
LOAD Key, sales_date, Cost center
Resident Sales
Where not exists('chkKey', Key);

DROP Table chkKey;

 There can be other solutions like a relationship table but I usually use one of the two above.

mustafaelryah
Creator
Creator
Author

Ok, I will try

Brett_Bleess
Former Employee
Former Employee

If Ruben's post did work for you, please be sure to return to your thread and close it out by using the Accept as Solution button on his post to give him credit and let the other Members know that worked.

Here is a Design Blog post that may be helpful too:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Just in case, here is the base URL for that area in case you wish to search further for other posts there, this area is mostly how-to related posts, so will likely be handy going foward:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.