Multiple Tables with redundant data

[sunil-kumar5]

Hi Everyone,

I have five tables and all have duplicate records and null values. There is no master table.

1. License Information Table - Installation Date and Due Date- Common field  id is CODE 

2. Calls Table - Created Date - Common field id is CODE

3. Missed Call Table - DATE -  Common field CODE

4. Reference Table - Reference Date - Common field is CODE

Now my problem is I want to create a single year and month filter works for all table and well connected with each other.

Do I need to create 4 master calender and link these 4 master calender with Date bridge and create canonical date?

Or any other way is there to resolve it.

Thanks

Sunil Kumar

[BrunPierre]

IMO, I suggest you utilize the 4 master calendars as it may help answer follow on questions.

It's further elaborated here: Canonical Dates  

[marcus_sommer]

You may just concatenate all tables by harmonizing the date-fields, like:

t: load CODE, [Created Date] as Date, 'Created' as Datetype, 'Licence' as Source from Licence;
concatenate(t) load CODE, [Due Date] as Date, 'Due' as Datetype, 'Licence' as Source from Licence;
concatenate(t) load CODE, [Created Date] as Date, 'Created' as Datetype, 'Calls' as Source from Calls;
...

- Marcus