Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Need to add additional rows of data

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.

QV Question Loop.PNG

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

13 Replies
m_woolf
Master II
Master II

MarcoWedel

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

thanks

regards

Marco

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

m_woolf
Master II
Master II

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:

QlikCommunity_Thread_234558_Pic1.JPG

QlikCommunity_Thread_234558_Pic2.JPG

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
Not applicable
Author

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 ...

QlikCommunity_Thread_234558_Pic3.JPG

QlikCommunity_Thread_234558_Pic4.JPG

Anonymous
Not applicable
Author

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)/