Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;