Create a table with a list of dates

[#1]

Hi Guys,

I want to create a table based of a list of dates generated with a For loop:

Is there a way to create an Inline table based of a For loop? Or some other way?

This is an example of the For Loop I have:

vMonthNo = 1 to vMonthCount
Let vMinimum = AddMonths(vMinDate, 1);
   '$(vMinimum)' as DateList
    From [$(vMinimum)];
Next vMonthNo;

To make it clearer, I loaded a table with a minimum date of 11/1/2012 and maximum date of 8/4/2014

I want to create a table of 22 months starting with 11/1/2012, 12/1/2012, 1/1/2013....ETC.

The day does not matter, They should all be the 1st of the month.

Any help would be appreciated.

[MarcoWedel]

Load addmonths(date#('11/01/2012', 'MM/DD/YYYY'), iterno()-1) as date

Autogenerate 1

While iterno() <= 22;

[jaimeaguilar]

Hi, I did something like this some time ago. I initially did it starting with an inline table, like this:

DateGenerator:

Load date(addmonths(FechaInicial, iterno()-1), 'YYYY-MM-DD') as FechaInicial,

    date(AddMonths(FechaFinal, iterno()-1), 'YYYY-MM-DD') as FechaFinal

While iterno() <= (12*(Year(TODAY())-Year(FechaInicial))+(Month(Today())-Month(FechaInicial))+1 );

LOAD * INLINE [

    FechaInicial, FechaFinal

    2008-01-01, 2014-01-31

];

Fecha inicial stands for Initial Date and Fecha Final for Final Date,

But then I improved and used preceding load, autogenerate and a formula that counts the months between the initial and the final date:

I attach a sample file,

regards

[#4]

Marco, That works, but how do I reference a previous loaded table to get a count. i.e. Count(field).

So I can replace 22 with a counter?

[MarcoWedel]

Hi,

one way, like this:

//test data generation
tabFieldData:
LOAD Rand() as field
AutoGenerate 22;

//Month dates generation
tabMonthDates:
LOAD AddMonths(Date#('11/01/2012', 'MM/DD/YYYY'), IterNo()-1) as date
While IterNo() <= MonthCount;
LOAD Count(field) as MonthCount
Resident tabFieldData;

hope this helps

regards

Marco

[MarcoWedel · Accepted solution]

Hi again,

as you mentioned a table with a minimum date of 11/1/2012 and maximum date of 8/4/2014, maybe you're looking for this solution instead of a field count:

//test data generation
tabTestData:
LOAD * Inline [
test dates
11/1/2012
12/10/2012
1/5/2013
2/12/2013
5/19/2013
9/15/2013
11/2/2013
2/9/2014
3/14/2014
6/13/2014
7/14/2014
8/4/2014
];

//Month dates generation
tabMonthDates:
LOAD AddMonths(MonthStart, IterNo()-1) as date
While AddMonths(MonthStart, IterNo()-1) <= MonthEnd;
LOAD MonthStart(Min([test dates])) as MonthStart,
    MonthStart(Max([test dates])) as MonthEnd
Resident tabTestData;

hope this helps even more

regards

Marco

[#7]

Marco,

Your second solution worked perfectly for me (date generator). Thank you very much for taking your time in helping me today.

[#8]

Thank you to everyone that collaborated in helping me with the question.

[MarcoWedel]

you're welcome.

Please mark this thread as answered.

thanks

regards

Marco