Autogenerate Dates up to second Max date

[MarieC]

Hi,

I would like to fill in the missing dates for the data below but only up until the second maxium date (in this case, 02/06/2022) but still maintain the max "end of world" date (31/12/9999) which is used as a placeholder date...

Month	Delivery Date	Weekday
May	27/05/2022	Fri
May	28/05/2022	Sat
June	02/06/2022	Thu
	31/12/9999

so would like to end up with something similar to the below...

Month	Delivery Date	Weekday
May	27/05/2022	Fri
May	28/05/2022	Sat
May	29/05/2022	Sun
May	30/05/2022	Mon
May	31/05/2022	Tue
Jun	01/06/2022	Wed
Jun	02/06/2022	Thu
	31/12/9999

Any thoughts on how I would go about doing this?

Thanks,

[Thiago_Justen_]

Hi there,

Try this out:

Temp_MaxMinDate:
Load Distinct
               min([Delivery Date])   As minDate,  
               max([Delivery Date],2) As maxDate  
Resident YOUR_TABLE; 

LET vMaxDate = Peek('maxDate');
LET vMinDate = Peek('minDate');

DROP Table Temp_MaxMinDate;

Load  
    $(vMinDate) + Iterno()-1 								As Num,  
    Date($(vMinDate) + IterNo() - 1) 						As Date 
    AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate)
; 

Hope it helps you!

[MarieC]

Hi Thiago,

Thanks for the suggestion. Is there a way to also include the Max date within the data but not autogenerate the dates from the second max to the max date?

I'm also having trouble making it work within my current calendar script (see below). How would you suggest i adapt my script?

Thanks again for your help.

SUB Calendar(Field, Name, Prefix)

LET vField = Field;
LET vCalendar = Name;
LET vPrefix = Prefix;

[$(vCalendar)]:
LOAD
	[$(vField)],
	Date([$(vField)]) as [$(vPrefix)date],
     Date([$(vField)],'MMM dd, wwww') as [$(vPrefix)date_weekday],
	Month([$(vField)]) as [$(vPrefix)month],
	Num(Month([$(vField)]),'00') as [$(vPrefix)month_no],
	MonthName([$(vField)]) as [$(vPrefix)month_year],
	Year([$(vField)]) as [$(vPrefix)year],
    WeekYear([$(vField)]) as [$(vPrefix)week_year],
	Num(Week([$(vField)]),'00') as [$(vPrefix)week],
	Day([$(vField)]) as [$(vPrefix)day],
	WeekDay([$(vField)]) as [$(vPrefix)week_day],
	Dual('Q' & Ceil(Month([$(vField)])/3),Ceil(Month([$(vField)])/3)) as [$(vPrefix)quarter]
;

LOAD
	Date(MinDate + IterNo()) as [$(vField)]
	WHILE MinDate + IterNo() <= MaxDate
;

LOAD
	Min(FieldValue('$(vField)',RecNo()))-1 as MinDate,
        Max(MakeDate(2030,12,31)) as MaxDate
 	AutoGenerate FieldValueCount('$(vField)')

;
    
LET vField =;
LET vCalendar =;
LET vPrefix =;

END SUB

CALL Calendar('%delivery_date','delivery_date_calendar','deliverydate_');