0 Replies Latest reply: Sep 28, 2015 1:38 PM by Jeff Kodish RSS

    Canonical Calendar Data Model

      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:


           Mapping Load Id, Campaign_Member_Date Resident CampaignMember ;


           Mapping Load Id, Task_Date Resident Task ;


           Mapping Load Id, CreatedDate Resident Contact ;



           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];





      rowno() as Month, 

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

      AUTOGENERATE (12); 




      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; 




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

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

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




        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;