Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my project, I have a master calendar with year, quarter, month, date, and day columns. I have another table comprising five distinct dimension columns, two measure columns, and one date column. In this table, I do not have rows for all 365 calendar dates but rather only for those days when the transactions (customer purchase) happened.
Using these two tables, I want to derive a third table that has a row for every single combination of the five dimension columns and calendar date. The values of the measures should be zero for those dimension + date combinations that are missing in my parent table and retain the original values for the combinations that exist in my parent table.
Please advise on how I can implement this logic in my load scripts.
considering in your 1st master calendar table, you have all 365 days data.
so just left join your transaction table to your master table. It will do the trick
for example
master:
Load *
from master_calendar;
Left join
Load *
from transaction;
Regards,
Prashant Sangle