I was unable to get that to work. I pulled each date into its own table like:
'1' as [ImpactStartDateFlag],
[Impact Start Date] as DateTemp
1'' as [CreatedDateFlag],
[Created Date] as DateTemp
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
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....