I have a time index consisting of actual dates:
Time := Sequence( start_date, end_date )
The start_date and end_date aren't on even year or even month boundaries.
I also have a 2-D representation of data across time that uses these two indexes:
Index Year := 2019 .. 2021 Index Date_of_year := ['1-Jan', '2-Jan', ..., '31-Dec' ]
BTW, my actual definitions for these are:
Index Year := Unique(DatePart(Time,'Y')) Index Date_of_year := Local d := Sequence(1-Jan-2020,31-Dec-2020) Do DatePart(d,'D') & '-' & DatePart(d,'EEE')
Â
Note that Date_of_year includes '29-Feb', even though non-leap years don't have data for that date (my arrays have Null in that case).Â
I want to compute a map from the 2-D Year x Date_of_year to the 1-D Time. In other words, the map would be a 2-D array, indexed by Year and Date_of_year, and each cell of that array would be the corresponding full date as it appears in the Time index. Some cells won't be valid dates (like 29-Feb-2021) and some will be outside by Time range since my Time index doesn't start on even year boundaries. Ideally those cells would be Null.Â
So, for example, the cell at [ Year = 2021, Date_of_year = '23-Jul' ] would be the date 23-July-2021.
The attached file sets up the problem -- I need the definition for Y_and_D_to_Time.
Local d := ParseDate(Day_of_year&"-"&Year); d := if Day_of_year='29-Feb' and DatePart(d,'D')<>29 then null else d; Time[Time=d,defVal:null]
Second line sets 29-Feb to Null for the non-leap years. The last line sets all cells in the map to Null if they don't exist in your Time index. In terms of using this as a mapping, you wouldn't need to do that last step (although it doesn't hurt), so this would be sufficient:
Local d := ParseDate(Day_of_year&"-"&Year); if Day_of_year='29-Feb' and DatePart(d,'D')<>29 then null else d
Either case should work fine to expand a 1-D array, x, that is indexed by Time to a 2-D array using
x[ Time = Y_and_D_to_Time ]
Â
Local isLeap := Mod(Year,4)=0 and Mod(Year,100)<>0 or Mod(Year,400)=1; Local offset := if Day_of_year='29-Feb' and not isLeap then null else if @Day_of_year > @[Day_of_year='29-Feb'] and not isLeap then @Day_of_year-2 else @Day_of_year-1; MakeDate(Year,1,1) + offset
