Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
This works! Thanks so much for the solution.
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