Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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;
ALL OK Except START_DATE... it should be starting from 00:00:00
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
I was almost there but only 86400 missed... How I dont know... Very stressful day !