Note that one quote number can have multiple rating requests
QuoteTable:
QuoteId
SystemId
Status
quote_date
2417
LifeWeb
Accepted
8/13/2020
2424
LifeWeb
Accepted
8/20/2020
2811
LifeWeb
Accepted
8/9/2020
And I'm trying to have one chart that shows the date and number of distinct rating requests and
another chart that shows the date and the number accepted. I have these two tables linked by quoteid and eventquotenumber (renamed them to QuoteNum). I tried using this code to make the canonical date:
DateBridge: Load QuoteNum, quote_date as CanonicalDate, 'Quote' as DateType Resident QuoteInfo;
Load QuoteNum, eventDateTime as CanonicalDate, 'Event' as DateType Resident TestEvents;
But when I do count({<EventName={'RatingRequest'}, DateType = {'Event'} >} distinct EventQuoteNumber)
tmpDateBridge: Load QuoteNum, Date(quote_date) as CanonicalDate, 'Quote' as DateType Resident QuoteTable;
Load QuoteNum, Date(eventDateTime) as CanonicalDate, 'Event' as DateType Resident EventTable;
NoConcatenate DateBridge: Load * Resident tmpDateBridge where IsNull(CanonicalDate) = 0;
Drop Table tmpDateBridge;
tmp: Load Min(CanonicalDate) as minCanonicalDate, Max(CanonicalDate) as maxCanonicalDate Resident DateBridge;
Let vMinDate = Num(Peek('minCanonicalDate',0,'tmpDateBridge')); Let vMaxDate = Num(Peek('maxCanonicalDate',0,'tmpDateBridge')); Drop table tmp;
TempCalendar: LOAD $(vMinDate) + Iterno()-1 As Num, Date($(vMinDate) + IterNo() - 1) as TempDate AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
MasterCalendar: Load TempDate AS [Canonical Date], TempDate, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Date(Date#(Year(TempDate)&'-'&Month(TempDate),'YYYY-MMM'),'YYYY-MM') as YearMnth, Day(TempDate) As Day, // ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay Resident TempCalendar Order By TempDate ASC;
