how to genarate in between records

[rahulhv1]

Hi Gurus

I have a set of records in date range  &  I want to create records for each date in the specified range. example is as under

Available records

       MACHINE NAME             FROM DATE           TO DATE

=================================================

      MCNO.1                             01 Jan 2015           05 Jan 2015

      MCNO.2                             12 Jan 2015           14 Jan 2015

.......

and like wise.

Desired out put is

      MACHINE NAME             DATE

=================================================

      MCNO.1                             01 Jan 2015           

      MCNO.1                             02 Jan 2015           

      MCNO.1                             03 Jan 2015           

      MCNO.1                             04 Jan 2015           

      MCNO.1                             05 Jan 2015           

      MCNO.2                             12 Jan 2015

      MCNO.2                             13 Jan 2015    

      MCNO.2                             14 Jan 2015

[ziadm]

HI

use the iterno function to loop through dates and generate the range

LOAD

   IterNo() as Day,

   Date( FromDate + IterNo() - 1 ) as Date

   While FromDate + IterNo() - 1 <= ToDate;

LOAD * INLINE

[FromDate, ToDate

2016-01-10, 2016-01-26

2016-02-05,2016-02-15

];

if this work please mark as helpful or correct

Thanks

Ziad

[ziadm]

sorry missed the Machine No

user this code

LOAD

   IterNo() as Day,

   Machine,

   Date( FromDate + IterNo() - 1 ) as Date

   While FromDate + IterNo() - 1 <= ToDate;

LOAD * INLINE

[Machine,FromDate, ToDate

MCNO1, 2016-01-10, 2016-01-26

MCNO2, 2016-02-05,2016-02-15

];

[rahulhv1]

Thanks Ziad. I got the solution & did in below manner.

Thanks again!!!

@

mcno:
LOAD mc,
jh,
lunch,
tea,date(startdate)as startdate1,date(enddate) as enddate,
if (date(startdate) < $(varMinDate),$(varMinDate),date(startdate))as stdt2
FROM

(ooxml, embedded labels, table is Sheet1) ;

mc2:
load mc,jh,lunch,tea,enddate ,startdate1,
Date( stdt2+IterNo()-1) as startdate2
Resident mcno while IterNo()<=enddate-stdt2+1;

[Kushal_Chawda]

Make sure that your FROM and TO dates are in proper date format else first use Date# function to make it in date format

Data:

LOAD [MACHINE NAME],

         date([FROM DATE] +iterno()-1) as DATE,

          [TO DATE]

FROM TABLE

while [FROM DATE] +iterno()-1 <= [TO DATE]

[MarcoWedel]

please close your thread if you got the solution.

thanks

regards

Marco