Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I was unable to get that to work. I pulled each date into its own table like:
DateResident:
LOAD
'1' as [ImpactStartDateFlag],
[Impact Start Date] as DateTemp
RESIDENT Source;
CONCATENATE
1'' as [CreatedDateFlag],
[Created Date] as DateTemp
RESIDENT Source;
And it gave me three unrelated tables (Master Calendar, Source, and DateResident
I also tried OUTER JOIN instead of CONCATENATE, because I thought it would be better to just append additional columns for each flag to each relevant date. For some reason, this would freeze half-way through. When animating the debug, it seemed to process the first few and then just lock up.
Isn't that the exact thing mentioned within canonical date?
DateBridge is a link table which is created by concatenating different dates from the table into a single table and all of them are named the CanonicalDate. OrderLineID is the unique identifier to tie it back to the main table. DataType is the flag. All like you mentioned
Do you have a unique identifier within Incident table?
I'm starting to realize this might be my missing piece. I have two data sources (two systems bringing in incident data). Each source has its own identifier, but source #2 also lists the ID from source #1 if it knows it (no guarantee). I am in the process of transforming the data from each of these so they combine into a single table. Unfortunately, they are pretty far apart at the source, so I've got my work cut out for me.
What I think I need to do is generate a separate key for the joined list. Once I have that, I will follow the Canonical Date instructions and see how it pans out.
As always, thanks for the help.
ok it is more clear now !
I always used this technique every time I had more than 1 type of date type in my model and I thought Canonical date was another different technique. (I know what is the main purpose, but never read the document)
Happens to all of us... I am scared to go through the Hierarchy syntax... have implemented it 2-3 times but never really understood what is going on....
Scared you say ? Come on.. I feel dead sometimes
Different unique identifiers should not be a problem.... AFAIK....