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.
- 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
LOAD distinct Key as chkKey resident Transactions;
// Add keys from Sales not already loaded in transactions
LOAD Key, sales_date, Cost center
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.
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:
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.