Need to add additional rows of data

[Anonymous]

I have a table with Start Times and End Times.  I need to add records for the hours between each Start and End Time for each Keyfield (RoomDateValueLINK).  I've tried using a loop, but it didn't work.  Can someone please assist?  Any help would be GREATLY appreciated.

[Anonymous · Accepted solution]

I'm attaching my QVW with sample data showing the existing table along with the expected results.  If someone has info on how to do this using LOOP (for... next...) please let me know.  Thank You.

[MarcoWedel]

Hi,

another example using a while clause in a resident load instead of an explicite for...next loop:

LOAD RoomDateValueLINK,
    StartTimeStamp,
    EndTimeStamp,
    ReservationType,
    StartHH,
    EndHH,
    HourDiffNUM,
    Minutes
FROM [https://community.qlik.com/servlet/JiveServlet/download/1134588-247899/WastedHoursSample.xls] (biff, embedded labels, table is Sheet1$);

WastedHoursNEW:
LOAD RoomDateValueLINK,
    Time(StartHH+(IterNo()-1)*'01:00','h:mm TT') as SchStartHOUR,
    ReservationType as ResType,
    If(IterNo()=1,Minutes,.0001) as Mins
While StartHH+(IterNo()-1)*'01:00' <= EndHH;
LOAD RoomDateValueLINK,
    ReservationType,
    Minutes,   
    Floor(Frac(StartTimeStamp),'01:00') as StartHH,
    Floor(Frac(EndTimeStamp),'01:00') as EndHH
Resident WastedHoursTmp;

maybe this helps

regards

Marco

[Anonymous]

This works!  Thanks so much for the solution. 

[MarcoWedel]

you're welcome.

Glad it helped.

Please close your thread by marking an answer correct that solved your problem.

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco