topic Re: Cycle loop with dates in QlikView

Cycle loop with dates

Thu, 05 Dec 2013 15:54:58 GMT

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

Re: Cycle loop with dates

stabben23 — Thu, 05 Dec 2013 16:33:39 GMT

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

Re: Cycle loop with dates

Thu, 05 Dec 2013 17:00:59 GMT

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

Re: Cycle loop with dates

Mon, 09 Dec 2013 15:47:02 GMT

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......