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.

[m_woolf]

Generating Missing Data In QlikView

[MarcoWedel]

please post some sample data/app and your expected result.

thanks

regards

Marco

[Anonymous]

I did post in the initial question.  Can you not see the image I added?

[Anonymous]

Does anybody have a solution for this?  I need to concatenate a specific number of records for each Keyfield.  Thanks in advance. 

[m_woolf]

If you open the pdf that I attached in an earlier post and look at the "Loops inside the Load statement" section, it describes how to fill in missing data with source data that contains an upper bound and a lower bound. That should be you StartHH and EndHH fields.

[MarcoWedel]

Hi,

maybe one solution might be:

tabRoomOccupancy:
LOAD *,
    Timestamp(Floor(StartTimeStamp+Rand()*(1-Frac(StartTimeStamp)),'00:10')) as EndTimeStamp;
LOAD 1000+RecNo() as RoomDateValueLINK,
    Timestamp(Floor(Today()-Rand()*20,'00:10')) as StartTimeStamp
AutoGenerate 10;

tabHours:
LOAD RoomDateValueLINK,
    Time(StartHH+(IterNo()-1)*'01:00','h TT') as StartHH,
    EndHH
While StartHH+(IterNo()-1)*'01:00' <= EndHH;
LOAD RoomDateValueLINK,
    Time(Floor(Frac(StartTimeStamp),'01:00'),'h TT') as StartHH,
    Time(Floor(Frac(EndTimeStamp),'01:00'),'h TT') as EndHH
Resident tabRoomOccupancy;

hope this helps

regards

Marco

[Anonymous]

Thanks for the suggestion, but this proposed solution is adding all missing hours to the table and would expand my table exponentially (my table has approx. 300,000 records and if I added 24 hours for every record then I would end up with over 7 million records).  I only need to add records for the Hours missing between STARTHH and ENDHH.

Here's how I'm doing it now using Concatenate.  It works, but I thought there might be a better solution:

WastedHoursTmp:
LOAD *,
(Num(Interval(EndHH - StartHH,'h'))*1440) / 60 as  HourDiffNUM;
LOAD
RoomDateValueLINK,
StartTimeStamp,
EndTimeStamp,
ReservationType,
Minutes
, TimeStamp(MakeTime(Num(Hour(StartTimeStamp),'00')),'h TT') as StartHH
, TimeStamp(MakeTime(Num(Hour(EndTimeStamp),'00')),'h TT')  as EndHH 
FROM
$(vQVDPath)WastedHours.qvd (qvd);

// GET ALL StartTimes
WastedHours:
LOAD
RoomDateValueLINK,
StartTimeStamp,
EndTimeStamp,
TimeStamp(MakeTime(Hour(StartHH)),'hh:mm TT') as SchStartHOUR,
TimeStamp(MakeTime(Hour(StartHH) + 1),'hh:mm TT') as SchStartHOUR1,
TimeStamp(MakeTime(Hour(StartHH) + 2),'hh:mm TT') as SchStartHOUR2,
TimeStamp(MakeTime(Hour(StartHH) + 3),'hh:mm TT') as SchStartHOUR3,
TimeStamp(MakeTime(Hour(StartHH) + 4),'hh:mm TT') as SchStartHOUR4,
TimeStamp(MakeTime(Hour(StartHH) + 5),'hh:mm TT') as SchStartHOUR5,
TimeStamp(MakeTime(Hour(StartHH) + 6),'hh:mm TT') as SchStartHOUR6,
TimeStamp(MakeTime(Hour(StartHH) + 7),'hh:mm TT') as SchStartHOUR7,
TimeStamp(MakeTime(Hour(StartHH) + 8),'hh:mm TT') as SchStartHOUR8,
TimeStamp(MakeTime(Hour(StartHH) + 9),'hh:mm TT') as SchStartHOUR9,
TimeStamp(MakeTime(Hour(StartHH) + 10),'hh:mm TT') as SchStartHOUR10,
TimeStamp(MakeTime(Hour(StartHH) + 11),'hh:mm TT') as SchStartHOUR11,
TimeStamp(MakeTime(Hour(StartHH) + 12),'hh:mm TT') as SchStartHOUR12 
,StartHH
,EndHH
,HourDiffNUM
,ReservationType
,Minutes
Resident WastedHoursTmp;
Drop Table WastedHoursTmp;

// CREATE NEW TABLE from WastedHours
WastedHoursNEW:
LOAD
RoomDateValueLINK,
SchStartHOUR,
ReservationType,
Minutes
Resident WastedHours
;
  // ADD 1 Hour Differences to new table
Concatenate (WastedHoursNEW)
LOAD
RoomDateValueLINK,
SchStartHOUR1 as SchStartHOUR ,

ReservationType,

.0001 as Minutes
Resident WastedHours
Where HourDiffNUM = 1
;
  // ADD 2 Hour Differences
Concatenate (WastedHoursNEW)
LOAD
RoomDateValueLINK,
SchStartHOUR2 as SchStartHOUR,
ReservationType ,

.0001 as Minutes
Resident WastedHours
Where HourDiffNUM >= 2
;
  // ADD 3 Hour Differences
Concatenate (WastedHoursNEW)
LOAD
RoomDateValueLINK, 
SchStartHOUR3 as SchStartHOUR,
ReservationType ,

.0001 as Minutes
Resident WastedHours
Where HourDiffNUM >= 3
;
  // ADD 4 Hour Differences
Concatenate (WastedHoursNEW)
LOAD
RoomDateValueLINK,
SchStartHOUR4 as SchStartHOUR,
ReservationType ,

.0001 as Minutes
Resident WastedHours
Where HourDiffNUM >= 4
;

As you can see I would have to continue Concatenating the SchStartHOUR(x) for each Hour Difference. 

I was hoping that there would be a better way to concatenate the records for the HourDiffNUM

[MarcoWedel]

actually it's creating a new table having all hours from start to end just like your script does ...

[Anonymous]

Thanks Marco for this additional QVW.  However, I see that you are using my Concatenate code.  I was hoping that there was a better way to add additional records instead of creating 24 Concatenates (1 for each missing hour).

My question is:  is there a way to do a Loop (For... Next...) that will create the x number of additional hours based on the HourDiffNum?  So if there are 7 hours between StartHH and EndHH then use a loop statement to add 7 rows of data (instead of using 7 Concatenate statements)/