Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

MVP
MVP

Loop

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

Input

PROJECT NOSTART_DATEEND_DATE
100105/01/2014 07:00:0005/01/2014 17:00:00
100230/01/2014 23:00:0006/02/2014 23:15:00
100320/03/2014 14:57:1523/03/2014 13:19:20

Output

PROJECT NOSTART_DATEEND_DATE
100105/01/2014 07:00:0005/01/2014 17:00:00
100230/01/2014 23:00:0030/01/2014 23:59:59
100231/01/2014 00:00:0031/01/2014 23:59:59
100201/02/2014 00:00:0001/02/2014 23:59:59
100202/02/2014 00:00:0002/02/2014 23:59:59
100203/02/2014 00:00:0003/02/2014 23:59:59
100204/02/2014 00:00:0004/02/2014 23:59:59
100205/02/2014 00:00:0005/02/2014 23:59:59
100206/02/2014 00:00:0006/02/2014 13:19:20
100320/03/2014 14:57:1520/03/2014 23:59:59
100321/03/2014 00:00:0021/03/2014 23:59:59
100322/03/2014 00:00:0022/03/2014 23:59:59
100323/03/2014 00:00:0021/03/2014 13:19:20
1 Solution

Accepted Solutions

Re: Loop

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

View solution in original post

4 Replies
MVP & Luminary
MVP & Luminary

Re: Loop

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;


talk is cheap, supply exceeds demand
MVP
MVP

Re: Loop

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

Re: Loop

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

View solution in original post

MVP
MVP

Re: Loop

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