Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
robynrshields
New Contributor III

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
robynrshields
New Contributor III

Re: Need to add additional rows of data

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.

13 Replies
mwoolf
Honored Contributor II

Re: Need to add additional rows of data

Re: Need to add additional rows of data

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

thanks

regards

Marco

robynrshields
New Contributor III

Re: Need to add additional rows of data

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

robynrshields
New Contributor III

Re: Need to add additional rows of data

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

mwoolf
Honored Contributor II

Re: Need to add additional rows of data

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.

Re: Need to add additional rows of data

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

robynrshields
New Contributor III

Re: Need to add additional rows of data

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

Re: Need to add additional rows of data

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

robynrshields
New Contributor III

Re: Need to add additional rows of data

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