Cycle loop with dates

[#1]

Hello to all,

how can I create a script, reading a row with a minimum

and a maximum for a date (DD/MM/YYYY), generates a row for each date.

For sample:

IdLev     Date1         Date2

1      11-01-2013      16-01-2013

2      17-01-2013      23-01-2013

3      24-01-2013      06-02-2013

Getting a table like this:

id_lev      DateNew

1           11-01-2013

1           12-01-2013

1           13-03-2013

..................

1           16-01-2013

2           17-01-2013

2           18-01-2013

..................

2      23-01-2013

3      24-01-2013

3      25-01-2013

3      26-01-2013

3      27-01-2013

3      28-01-2013

3      29-01-2013

3      30-01-2013

3      31-01-2013

3      01-02-2013

3      02-02-2013

3      03-02-2013

3      04-02-2013

3      05-02-2013

3      06-02-2013

Somebody can help me?

    Thank a lot

[stabben23 · Accepted solution]

This will work:

Load * Inline [

IdLev ,  Date1   ,   Date2
1   ,   11-01-2013   ,   16-01-2013
2  ,    17-01-2013   ,   23-01-2013
3  ,   24-01-2013   ,   06-02-2013
];

MinMaxDate:
Load distinct
IdLev,
min(Date1) as MinDate,
max(Date2) as MaxDate
resident Test
group by IdLev
order by IdLev;

Let vMinDate = Peek('MinDate',0,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Calendar:
Load recno()+$(vMinDate)-1 as Date
Autogenerate vMaxDate - vMinDate;

JOIN (MinMaxDate) INTERVALMATCH (Date) LOAD MinDate, MaxDate
RESIDENT MinMaxDate;

Drop Table Test;
Drop Table Calendar;
Drop Field MinDate;
Drop Field MaxDate;

//Staffan

[#3]

Another way is to use the IterNo() function with the While boolean statement

if you load the first dates into a temp_table, you could write:

Table2:
IDlev,

date(date(Date1)+iterno()-1) as Date1
Resident Temp_Table

While date(Date1)+Iterno() <= date(Date2);

drop table Temp_Table;

Fabrice

[#4]

Thanks a lot.

The solution proposed by you works fine

Fabrice:

I made a small change to your solution why it does not extract the last date for each value of Idlev:

Table2:

LOAD IDlev,

date(date(Date1)+iterno()-1) as Date1

Resident Temp_Table

While date(Date1)+Iterno() -1 <= date(Date2);       //  <------

drop table Temp_Table;

Thank 1000......