topic Re: Loop in QlikView

Loop

MK_QSL — Tue, 01 Jul 2014 14:29:39 GMT

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

Re: Loop

Gysbert_Wassenaar — Tue, 01 Jul 2014 14:43:18 GMT

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;

Re: Loop

MK_QSL — Tue, 01 Jul 2014 14:59:57 GMT

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

Re: Loop

MarcoWedel — Tue, 01 Jul 2014 15:10:17 GMT

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

Re: Loop

MK_QSL — Tue, 01 Jul 2014 15:29:20 GMT

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