How can I create whole sequence of dates from ‘monthYear ‘ to 01-01-2020, 01-02-2020, …….12-31-2020 ?

[nezuko_kamado]

I have  a column ‘monthYear ‘ [Dec-19, Jan-20, Feb-20, Mar-20,  ......   Dec-20] from my data, and how can I create whole sequence of dates like   12-01-2019,12-31-2019, …..  01-01-2020,    ……. 12-31-2020?

[rubenmarin · Accepted solution]

Hi, ther are many ways to create a calendar, ie:

LOAD 
	 IdDate,
	 Date(IdDate)					as Date,
	 Month(IdDate) 				as Month,
	 Num(Month(IdDate))				as NumMonth,
	 Year(IdDate) 					as Year
;
LOAD 
	 MinDate + IterNo()-1 				as IdDate
While MinDate + IterNo()-1 <= MaxDate
;
LOAD 
	 Floor(Min(FieldValue('monthYear', RecNo()))) 	as MinDate,
	 Floor(Max(FieldValue('monthYear', RecNo()))) 	as MaxDate
AutoGenerate FieldValueCount('monthYear');

If the values stored on monthYear are dates that would work to create al dates betwen the min and max date. If monthYear is a string you can use:

Date#(FieldValue('monthYear', RecNo()), 'MMM-YY')
//instead of: FieldValue('monthYear', RecNo())

[nezuko_kamado]

Thank you! it works. Then the last question is why AutoGenerate() can't work with Resident?

I used Resident instead of AutoGenerate() but how can I  use the both term?

[rubenmarin]

Hi those are 2 different origins so they can't be combined

- Resident loads from a table.

- Autogenerate loads a specific number of records (from no particular table).

The way this autogenerate works usually gives better reload times as it only reads the different values of the date field, that is what you need to create the calendar.

Think in fact table with millions of rows, using resident will read all those millions rows, autogenerate will only read the different months.