Canonical Calendar Data Model

[#1]

I'm having a tough time setting up my canonical calendar, and could really use some help. I have data connected to Salesforce and an Excel spreadsheet.

So far I have (I think) successfully created a canonical calendar with Id as the key, including the DateTypes CreatedDate, CampaignMemberDate, and TaskDate.

Now I want to link the DorgDate, which is in my spreadsheet, to the canonical calendar.

My data model has the DateBridge branching off the Contact table, with Id as key, and I can't figure out how to get the DorgDate, which is linked to Account_Name as a key. I've read the blog, but it doesn't go into enough depth, i.e:

"First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated"

I don' t think I have a table like that, and am not sure how to create one to adapt.

Load Script:

MapIDtoCampaignMemDate:

     Mapping Load Id, Campaign_Member_Date Resident CampaignMember ;

MapIDtoTaskDate:

     Mapping Load Id, Task_Date Resident Task ;

MapIDtoCreatedDate:

     Mapping Load Id, CreatedDate Resident Contact ;

DateBridge:

     Load Id, Applymap('MapIDtoCreatedDate',Id,Null()) as CanonicalDate, 'Created' as DateType

          Resident [Contact];

     Load Id, Applymap('MapIDtoTaskDate',Id,Null()) as CanonicalDate, 'Task' as DateType

          Resident [Task];

     Load Id, Applymap('MapIDtoCampaignMemDate',Id,Null()) as CanonicalDate, 'CampaignMember' as DateType

          Resident [CampaignMember];

CanonicalCalendar:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

min(CanonicalDate) as minDate, 

max(CanonicalDate) as maxDate 

Resident DateBridge; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

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

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

CanonicalCalendar: 

Load 

  TempDate As CanonicalDate,

  week(TempDate) As CanonicalWeek, 

  Year(TempDate) As CanonicalYear, 

  Month(TempDate) As CanonicalMonth, 

  Day(TempDate) As CanonicalDay, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as CanonicalQuarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CanonicalWeekYear, 

  WeekDay(TempDate) as CanonicalWeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;