Create 4 hour increments of time from start and end date

[tchovanec]

I have attached an sample application. What i am trying to do is create 4 hour increments of time. If a row has a start time of 7:00 AM and an end time of 7:00 PM, I would like to create a row for 7a-11a then 11a-3p and so on until I hit 7:00PM. Below is the result I am trying to get from the attached application. Any help would be greatly appreciated. Thank you.

MemberID	StartTime	EndTime	Org	Role
100	10/19/2016 11:00:00 PM	10/20/2016 03:00:00 AM	1	2000
100	10/20/2016 03:00:00 AM	10/20/2016 07:00:00 AM	1	2000
100	10/20/2016 07:00:00 AM	10/20/2016 11:00:00 AM	1	2000
100	10/19/2016 07:00:00 AM	10/19/2016 11:00:00 AM	1	2000
200	10/18/2016 07:00:00 AM	10/18/2016 11:00:00 AM	2	2500
200	10/18/2016 11:00:00 AM	10/18/2016 03:00:00 PM	2	2500
200	10/18/2016 03:00:00 PM	10/18/2016 07:00:00 PM	2	2500
200	10/18/2016 07:00:00 PM	10/18/2016 11:00:00 PM	2	2500
200	10/20/2016 07:00:00 AM	10/20/2016 11:00:00 AM	2	2500
200	10/20/2016 11:00:00 AM	10/20/2016 03:00:00 PM	3	2500
200	10/20/2016 03:00:00 PM	10/20/2016 07:00:00 PM	3	2500
200	10/20/2016 07:00:00 PM	10/20/2016 11:00:00 PM	3	2500

[Digvijay_Singh]

Having personal edition so cannot view your file, I think below script can help -

Source:

Load * inline [

MemberID, StartTime, EndTime,Org, Role

100, 10/19/2016 11:00:00 PM, 10/20/2016 11:00:00 AM, 1, 2000

100, 10/19/2016 07:00:00 AM, 10/19/2016 11:00:00 AM, 1, 2000

200, 10/18/2016 07:00:00 AM, 10/18/2016 11:00:00 PM, 2, 2500

200, 10/20/2016 07:00:00 AM, 10/20/2016 11:00:00 PM, 2, 2500

200, 10/20/2016 11:00:00 AM, 10/20/2016 03:00:00 PM, 3, 2500

];

NoConcatenate

Final:

Load MemberID,

  If(Iterno()>1 and (StartTime + 4*IterNo()/24)<=EndTime, Peek(EndTime),StartTime) as StartTime,

  If(StartTime + 4*IterNo()/24<=EndTime,TimeStamp(StartTime + 4*IterNo()/24),EndTime) as EndTime,

  Org, Role

Resident Source

While StartTime + 4*IterNo()/24 <= EndTime;

drop table Source;

[tchovanec]

Digvijay, thank you for the response. It is very close to working, but for some reason the row below does not generate the 3:00 PM - 7:00 PM row

Source:

Load * inline [

MemberID, StartTime, EndTime,Org, Role

200, 10/20/2016 11:00:00 AM, 10/20/2016 07:00:00 PM, 3, 2500

];

[Digvijay_Singh]

You are right but bit strange, looks like they had difference in fraction of seconds internally. I now used floor in comparison so that they are compared up to the seconds precision. It now worked for me. Also simplified Start time line as we don't need other condition it seems.

Source:

Load * inline [

MemberID, StartTime, EndTime,Org, Role

100, 10/19/2016 11:00:00 PM, 10/20/2016 11:00:00 AM, 1, 2000

100, 10/19/2016 07:00:00 AM, 10/19/2016 11:00:00 AM, 1, 2000

200, 10/18/2016 07:00:00 AM, 10/18/2016 11:00:00 PM, 2, 2500

200, 10/20/2016 07:00:00 AM, 10/20/2016 11:00:00 PM, 2, 2500

200, 10/20/2016 11:00:00 AM, 10/20/2016 03:00:00 PM, 3, 2500

200, 10/20/2016 11:00:00 AM, 10/20/2016 07:00:00 PM, 3,  2500

];

NoConcatenate

Final:

Load MemberID,

  If(Iterno()>1, Peek(EndTime),StartTime) as StartTime,

  If(StartTime + 4*IterNo()/24<=EndTime,TimeStamp(StartTime + 4*IterNo()/24),EndTime) as EndTime,

  Org, Role

Resident Source

While floor(StartTime + 4*IterNo()/24,1/(24*60*60))<=floor(EndTime,1/(24*60*60));

drop table Source;

[rwunderlich]

It's a variance in how times are calculated/rounded. See Correct Time Arithmetic | Qlikview Cookbook.  I've tried various rounding tricks but the only reliable method I've found is to reinterpret with Timestamp#() as I show in the post. Here's how that works with your data.

TEST:

LOAD *, RecNo() as RecId INLINE [

    MemberID, StartTime, EndTime, Org, Role

    100, 10/19/2016 11:00:00 PM, 10/20/2016 11:00:00 AM, 1, 2000

    100, 10/19/2016 07:00:00 AM, 10/19/2016 11:00:00 AM, 1, 2000

    200, 10/18/2016 07:00:00 AM, 10/18/2016 07:00:00 PM, 2, 2500

    200, 10/20/2016 07:00:00 AM, 10/20/2016 11:00:00 AM, 2, 2500

    200, 10/20/2016 11:00:00 AM, 10/20/2016 07:00:00 PM, 3, 2500

];

Expanded:

NoConcatenate

LOAD RecId,

  MemberID,

  Timestamp(StartTime + interval#((IterNo()-1)*4, 'h')) as StartTime,

  Timestamp(StartTime + interval#((IterNo())*4, 'h')) as EndTime,

  Org,

  Role

Resident TEST

While TimeStamp#(TimeStamp(StartTime + interval#((IterNo())*4, 'h'),'YYYYMMDDhhmmss'),'YYYYMMDDhhmmss') <= EndTime

;

DROP TABLE TEST;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[james_butler]

Great post, thank you!

[james_butler]

Hi Rob and Digvijay

I've worked both of your methods through and I'm getting the same result.

Rather than 4 hourly, my data set meets to be hourly so I have amended my script to be:

In the front end I am summing up "Carparkprebooking Car Count".

For some reason the expression works for every 3 hours but not for the 2 hours in between.

For example 00:00 = 112 which is correct

But at 01:00 I've got 32 and 80. Added together they are correct but I can't figure out why I've got  two timestamps.

Any help would be much appreciated.

Thanks

James

[rwunderlich]

It would be good to start a new thread with some sample data.

-Rob

[james_butler]

Done

Using IterNo() to create hourly increments

[james_butler]

Thanks