Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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!
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_');