Master Calendar Joining Issue

[murti]

Hey, I created a simple data sources which has multiple tables that contains distinct date columns which i wanted to examine in a master calendar for simplicity purposes. 

DateTemp:
LOAD
Min(agreement_date) as MinDate,
Max(agreement_date) as MaxDate
RESIDENT ContractsOnActiveSchools;

LOAD
Min(first_payment_date) as MinDate,
Max(first_payment_date) as MaxDate
RESIDENT FirstTimePayments;

LET vMinDate = Num(Peek('MinDate', 0, 'DateTemp'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'DateTemp'));

DROP TABLE DateTemp;

calendar_temp:
Load
Date($(vMinDate) + IterNo() - 1,'YYYY-MM-DD') as date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

calendar:
load
date,
Year(date) as year,
month(date) as month,
WeekDay(date) as weekDay,
date(WeekStart(date),'YYYY-MM-DD') as weekStartDate,
date(floor(MonthStart(date)), 'MMM-YYYY') as yearMonth,
day(date) as daynumberMonth
resident calendar_temp;

DROP TABLE calendar_temp;

Here is the master calendar approach which seems like working correctly. However, when I want to make the left join operations 

LEFT JOIN (FirstTimePayments)
LOAD
date,
year,
month,
weekDay,
yearMonth
RESIDENT calendar
WHERE date = first_payment_date;

it says first_payment_date not found 

LOAD id as payment_id,
customer_id,
customer_agreement_id,
payment_type_id,
status_id,
amount as first_month_amount,
installment,
Date(first_payment_date) as first_payment_date,
Year(first_payment_date) as first_payment_date_year,
Month(first_payment_date) as first_payment_date_month,
Day(first_payment_date) as first_payment_date_day,
recurring_month,
payment_due_date,
last_recurring_at;

here is how i put the FirstTimePayments into the script, the sections are ordered correctly and i wanted to create a date key that might refers all the date fields in all the tables so that i will examine all the date related information into one filter pane but due to this error i could not make any progress. Your helps will be appreciated..

[TauseefKhan · Accepted solution]

Hi @murti,

The LEFT JOIN is trying to join the calendar table with the FirstTimePayments table on the date field, but it cannot find the first_payment_date field because it's not available in the scope of the calendar table at the time of the join.

To resolve this, you need to ensure that the first_payment_date field is available in the FirstTimePayments table before you attempt to join it with the calendar table. 

// Load the FirstTimePayments table with the necessary fields First 

FirstTimePayments:
LOAD
id as payment_id,
customer_id,
customer_agreement_id,
payment_type_id,
status_id,
amount as first_month_amount,
installment,
Date(first_payment_date) as first_payment_date,
Year(first_payment_date) as first_payment_date_year,
Month(first_payment_date) as first_payment_date_month,
Day(first_payment_date) as first_payment_date_day,
recurring_month,
payment_due_date,
last_recurring_at
RESIDENT YourSourceTable; // Replace with the actual source table name

// Now perform the LEFT JOIN operation
LEFT JOIN (FirstTimePayments)
LOAD
date,
year,
month,
weekDay,
yearMonth
RESIDENT calendar
WHERE Exists(first_payment_date, date);

 *** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. ***