Hello,
I have 3 different dates, of which 2 dates are in one table and 1 is in another. I have managed to succesfully create a Canonical calendar and master calendar for the two dates within the same table. However, when I try to link the date from the third table into the Master calendar I get a warning that there is circular reference.
Just for context, the table customer_loan_payments and customer_loans can be joined on customer_loan_id-id, customer_loan_payments being more granular so I cannot just concatenate both tables because I would just obtain duplicates.
Here is my script:
DateBridgeCLP:
Load
'paid_date' as Flag_Date,
[customer_loan_payments.id],
paid_date as Canonical_Date
Resident customer_loan_payments;
Load
'due_date' as Flag_Date,
[customer_loan_payments.id],
due_date as Canonical_Date
Resident customer_loan_payments;
/* until here everything works perfectly but here is my script to link the third date from the second table*/
DateBridgeCL:
Load
'buy_date' as Flag_Date,
[customer_loan_id-id],
buy_date as Canonical_Date
Resident customer_loans;
/*and here is the master calendar linking everything*/
MasterCalendar:
LOAD
Date(TempDate) AS Canonical_Date,
Year(TempDate) AS Year,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM/YYY') AS MonthYear,
Day(TempDate) As Day,
Week(TempDate) As Week;
Load
MinDate + IterNo() -1 AS TempDate
While (MinDate + IterNo() - 1) <= MaxDate;
Load
Min(FieldValue('Canonical_Date',RecNo())) -1 AS MinDate,
Max(FieldValue('Canonical_Date',RecNo())) AS MaxDate
AUTOGENERATE FieldValueCount('Canonical_Date');
