simple date matching

[matt_lynxx]

I have created a calendar and want to link a seperate table to the calendar using the year and month, but when i apply the below load scrip the seperate table does not link to the calendar date variable i expect it to, it is just appending the new table values as new rows.

I am expecting the CA_year_month field to match for Dec 2022 - Feb 2022

SET DateFormat='YYYY-MM-DD';

let StartDate = date('2022-12-15', 'YYYY-MM-DD');
let EndDate = date(Today(), 'YYYY-MM-DD');
let vStartNum = num('$(StartDate)');
let vEndNum = num('$(EndDate)');

for i = $(vStartNum) to $(vEndNum)
let vdate = Date(i, 'YYYY-MM-DD');
let vDay = Day(vdate);
let vDateNum = num(Date(Floor(vdate)));
let vWeekday = WeekDay(vdate);
let vWeekstart = Weekstart(vdate);
let vWeekend_sunday = Weekend(vdate);
let vWeekend_friday = date(Weekend(vdate) -2);
let vMonth = dual(Month(Date(floor(vdate))), num(Month(Date(Floor(vdate)))));
let vMonthNum = num(Month(Date(Floor(vdate))), '00');
let vYear = Year(Date(vdate));
let vYearMonth = dual(Year(date(floor(vdate))) & '-' & Month(date(floor(vdate))), MonthStart(date(floor(vdate))));
let vYearMonthNum = num(Year(date(floor(vdate)))) & Month(date(floor(vdate)));


calendar:
Load * Inline
[%date, CA_Date, CA_Day, CA_DateNum, CA_Weekday, CA_Weekstart, CA_Weekend_sunday, CA_Weekend_friday, CA_Month, CA_MonthNum, CA_Year, CA_year_month,CA_year_month_num
$(vdate), $(vdate), $(vDay), $(vDateNum), $(vWeekday), $(vWeekstart), $(vWeekend_sunday), $(vWeekend_friday), $(vMonth), $(vMonthNum), $(vYear), $(vYearMonth),$(vYearMonthNum)
];

next i;

LOAD reporting_period,
dual(Year(date(floor(Date(reporting_period,'YYYY-MM-DD')))) & '-' & Month(date(floor(Date(reporting_period,'YYYY-MM-DD')))),
MonthStart(date(floor(Date(reporting_period,'YYYY-MM-DD'))))) as CA_year_month,
Punctuality,
Reliability
Inline [
reporting_period, Punctuality, Reliability
'2022-12-01', .807, .9847
'2023-01-01', .854, .978
'2023-02-01', .999, .999 // placeholder values
];

This scrip produces this output, notice there are 2 rows for 2022-Dec, 2023 Jan, and 2023 Feb. These should be one row each. The dates are obviously not matching each other, but i dont know what could be causing this.

[Lisa_P · Accepted solution]

The formatting is different. If you take away the dual function this works.

[MayilVahanan]

Hi

Instead of "For" loop, you can modified with autogenerate functionality like below

SET DateFormat='YYYY-MM-DD';

let StartDate = date('2022-12-15', 'YYYY-MM-DD');
let EndDate = date(Today(), 'YYYY-MM-DD');
let vStartNum = num('$(StartDate)')-1;
let vEndNum = num('$(EndDate)');

Date:
LOAD *, Date(%date) as CA_Date, Day(%date) as CA_Day, floor(%date) as CA_DateNum, WeekDay(%date) as CA_Weekday,
dual(Year(%date) & '-' & Month(%date), MonthStart(%date)) as CA_year_month;
Load $(vStartNum)+IterNo() as %date AutoGenerate 1 While $(vStartNum)+IterNo() <= $(vEndNum);

LOAD reporting_period,
dual(Year(date(floor(Date(reporting_period,'YYYY-MM-DD')))) & '-' & Month(date(floor(Date(reporting_period,'YYYY-MM-DD')))),
MonthStart(date(floor(Date(reporting_period,'YYYY-MM-DD'))))) as CA_year_month,
Punctuality,
Reliability
Inline [
reporting_period, Punctuality, Reliability
'2022-12-01', .807, .9847
'2023-01-01', .854, .978
'2023-02-01', .999, .999 // placeholder values
];

In that case, it will work without any issue and it will load much faster when compare to "For loop".