Linking dates from different tables

[Jacob_Weig]

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');

[vikasmahajan]

Hi,

Might be link table https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

Hope this will help you

Vikas