topic Re: Need to add additional rows of data in QlikView

Need to add additional rows of data

Anonymous — Wed, 28 Sep 2016 19:21:16 GMT

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.

Re: Need to add additional rows of data

m_woolf — Wed, 28 Sep 2016 19:44:55 GMT

Generating Missing Data In QlikView

Re: Need to add additional rows of data

MarcoWedel — Wed, 28 Sep 2016 20:15:07 GMT

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

thanks

regards

Marco

Re: Need to add additional rows of data

Anonymous — Wed, 28 Sep 2016 20:17:03 GMT

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

Re: Need to add additional rows of data

Anonymous — Thu, 29 Sep 2016 14:11:53 GMT

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

Re: Need to add additional rows of data

m_woolf — Thu, 29 Sep 2016 19:26:01 GMT

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

MarcoWedel — Thu, 29 Sep 2016 20:49:15 GMT

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

Re: Need to add additional rows of data

Anonymous — Thu, 29 Sep 2016 21:45:20 GMT

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

MarcoWedel — Thu, 29 Sep 2016 22:15:00 GMT

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

Re: Need to add additional rows of data

Anonymous — Fri, 30 Sep 2016 14:39:16 GMT

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

Re: Need to add additional rows of data

Anonymous — Fri, 30 Sep 2016 15:40:55 GMT

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.

Re: Need to add additional rows of data

MarcoWedel — Sat, 01 Oct 2016 22:06:25 GMT

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

Re: Need to add additional rows of data

Anonymous — Wed, 05 Oct 2016 13:27:14 GMT

This works! Thanks so much for the solution.

Re: Need to add additional rows of data

MarcoWedel — Wed, 05 Oct 2016 21:31:10 GMT

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