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

    Canonical Calendar Data Model

    Jeff Kodish

      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.


       

      Capture.PNG

      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;