How to generate canonical calender for no common field

[Anonymous]

Hi All

i have 4 tables like

Employee,Manager,Service,Admin having date fields as EmployeeDate,Manager1Date,Manager2Date,ServiceDate,AdminDate

for this i need to generate master calender for all the 4 tables

and there is no common field in 4 tables

how to generate plz help me

Regards,

Mahesh

[marcus_sommer]

For canonical dates look here: Canonical Date and more to master-calendars could you find here: How to use - Master-Calendar and Date-Values

- Marcus

[Anonymous]

May be link table as an option?

Emp:

Load * From Employeetable;

Man:

Load * From ManagerTable;

Ser:

Load * From ServiceTable;

Adm:

Load * From AdminTable;

Now take resident of table with a flag indicator like below:

Calender:

Load EmployeeDate as Date, 'Employee' as Flag resident Emp;

Load Manager1Date as Date, 'Manager' as Flag resident Emp;

Load ServiceDate as Date, 'Service' as Flag resident Emp;

Load AdminDate as Date, 'Admin' as Flag resident Emp;

Now you can create a Year,Day,Month on this Date fileds

[Anonymous]

@Balraj

with this i get total dates in date field

but there is no link between date that we created and employeedate,managerdate ..............

how can we link them so that the master calender date will show all the dates

[Anonymous]

Any Common Unique number like Employee ID in all tables?

If yes, then take that ID as resident in Calender, it will work.

[sunny_talwar · Accepted solution]

May be something like this:

EMPLOYEE:

LOAD * Inline [

Employee, EmployeeDate

xyz, 02/03/2015

abc, 06/15/2014

];

MANAGER:

LOAD * Inline [

Manager, ManagerDate

xyz, 03/25/2015

abc, 09/20/2014

];

SERVICE:

LOAD * Inline [

Service, ServiceDate

xyz, 01/17/2015

abc, 03/09/2014

];

ADMIN:

LOAD * Inline [

Admin, AdminDate

xyz, 11/16/2015

abc, 10/10/2014

];

LINK_TABLE:

LOAD EmployeeDate as Date,

  EmployeeDate,

  'Employee' as DateType

Resident EMPLOYEE;

Concatenate(LINK_TABLE)

LOAD ManagerDate as Date,

  ManagerDate,

  'Manager' as DateType

Resident MANAGER;

Concatenate(LINK_TABLE)

LOAD ServiceDate as Date,

  ServiceDate,

  'Service' as DateType

Resident SERVICE;

Concatenate(LINK_TABLE)

LOAD AdminDate as Date,

  AdminDate,

  'Admin' as DateType

Resident ADMIN;

Temp:

LOAD Max(Date) as MaxDate,

  Min(Date) as MinDate

Resident LINK_TABLE;

LET vMaxDate = Peek('MaxDate');

LET vMinDate = Peek('MinDate');

DROP Table Temp;

OPEN_TEMP_CALENDAR:

LOAD $(vMinDate) + Iterno()-1 As Num,

  Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(vMinDate) + IterNo() -1 <= $(vMaxDate); 

MASTER_CALENDAR:

LOAD TempDate as Date,

  Week(TempDate) as WEEK,

  WeekStart(TempDate) as WEEK_START,

  WeekEnd(TempDate) as WEEK_END,

  Year(TempDate) as YEAR,

  Month(TempDate) as MONTH,

  Day(TempDate) as DAY,

  YearToDate(TempDate)*-1 as CUR_YTD_FLAG,

  YearToDate(TempDate,-1)*-1 as LAST_YTD_FLAG,

  InYear(TempDate, MonthStart($(vMaxDate)),-1) as RC12,

  MonthName(TempDate) as MONTHYEAR,

  ApplyMap('QuartersMap', Month(TempDate), Null()) as QUARTER,

  Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WEEKYEAR,

  WeekDay(TempDate) as WEEKDAY

Resident OPEN_TEMP_CALENDAR

Order By TempDate;

DROP Table OPEN_TEMP_CALENDAR;