Hi. I am looking to add a Master Calendar to an incident tracker application. I am familiar with a basic Master Calendar, where every key ID is associated with a single date (one event happens on one date, which associates cleanly to the calendar).
However, this new application has multiple different dates:
Created Date; Completed Date; Impact Start Date; Impact End Date, and others.
How can I associate all of these with a calendar? I would like any one of them to have access to the functions in the calendar (numbering months, weeks, and quarters, flagging year-to-date, etc). If I select a single date, I would like to see anything created on that date, completed on that date, or otherwise associated with that date.
Incidents:
LOAD
[Created Date],
[Completed Date],
[Impact Start Date]…….
FROM source.xlsx;
Then I use all of these combined to create a Min and Max date to build the calendar:
TempMinMaxDate1:
LOAD
MIN([Created Date]) as MinDateTemp,
MAX([Created Date] as MaxDateTemp,
RESIDENT Incidents;
LOAD
MIN([Completed Date]) as MinDateTemp,
MAX([Completed Date]) as MaxDateTemp
RESIDENT Incidents;
{and so on, joining all date functions into a single Min or Max field}
TempMinMaxDate:
LOAD
MIN (MinDateTemp) as MinDate,
Max)MaxDateTemp) as MaxDate
RESIDENT TempMinMaxDate1;
Giving me a single Min Date and MaxDate to feed the rest of the standard Master Calendar function. But this information is not associated with the source data any longer. Please see the attached PNG to show the resulting table structure
Can you offer a suggestion on how to tie this together, so that the calendar functions are related to the dates in the source material, regardless of the type of date?
