Loop

[MK_QSL]

Need to create a loop in script to get output table...

Input

PROJECT NO	START_DATE	END_DATE
1001	05/01/2014 07:00:00	05/01/2014 17:00:00
1002	30/01/2014 23:00:00	06/02/2014 23:15:00
1003	20/03/2014 14:57:15	23/03/2014 13:19:20

Output

PROJECT NO	START_DATE	END_DATE
1001	05/01/2014 07:00:00	05/01/2014 17:00:00
1002	30/01/2014 23:00:00	30/01/2014 23:59:59
1002	31/01/2014 00:00:00	31/01/2014 23:59:59
1002	01/02/2014 00:00:00	01/02/2014 23:59:59
1002	02/02/2014 00:00:00	02/02/2014 23:59:59
1002	03/02/2014 00:00:00	03/02/2014 23:59:59
1002	04/02/2014 00:00:00	04/02/2014 23:59:59
1002	05/02/2014 00:00:00	05/02/2014 23:59:59
1002	06/02/2014 00:00:00	06/02/2014 13:19:20
1003	20/03/2014 14:57:15	20/03/2014 23:59:59
1003	21/03/2014 00:00:00	21/03/2014 23:59:59
1003	22/03/2014 00:00:00	22/03/2014 23:59:59
1003	23/03/2014 00:00:00	21/03/2014 13:19:20

[Gysbert_Wassenaar]

Something like this:

LOAD [PROJECT NO].

     if( DayEnd(START_DATE + iterno()) <= END_DATE,

          DayStart(START_DATE + iterno()-1),

          START_DATE)  as START_DATE,

     if( DayEnd(START_DATE + iterno()-1) < END_DATE,

          DayEnd(START_DATE + iterno()-1),

          END_DATE)  as END_DATE

FROM ....

WHILE DayEnd(START_DATE + iterno()-1) < END_DATE;

[MK_QSL]

ALL OK Except START_DATE... it should be starting from 00:00:00

[MarcoWedel · Accepted solution]

Hi Manish,

although I have no doubt you could create this script on your own :

LOAD [PROJECT NO],
    If([PROJECT NO]=Peek([PROJECT NO]), Timestamp(Floor(START_DATE)+IterNo()-1), START_DATE) as START_DATE,
    If(Floor(START_DATE)+IterNo()-1/86400<END_DATE, Timestamp(Floor(START_DATE)+IterNo()-1/86400), END_DATE) as END_DATE
FROM [http://community.qlik.com/thread/123977] (html, codepage is 1252, embedded labels, table is @1)
While Floor(START_DATE)+IterNo()-1<END_DATE;

regards

Marco

[MK_QSL]

I was almost there but only 86400 missed... How I dont know... Very stressful day !