topic Re: Create 4 hour increments of time from start and end date in QlikView

Create 4 hour increments of time from start and end date

tchovanec — Wed, 10 May 2017 20:33:19 GMT

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

Re: Create 4 hour increments of time from start and end date

Digvijay_Singh — Thu, 11 May 2017 04:45:17 GMT

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;

Re: Create 4 hour increments of time from start and end date

tchovanec — Thu, 11 May 2017 11:21:06 GMT

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

];

Re: Create 4 hour increments of time from start and end date

Digvijay_Singh — Thu, 11 May 2017 15:24:26 GMT

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;

Re: Create 4 hour increments of time from start and end date

rwunderlich — Thu, 11 May 2017 15:26:46 GMT

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

Re: Create 4 hour increments of time from start and end date

james_butler — Tue, 30 Oct 2018 16:06:50 GMT

Great post, thank you!

Re: Create 4 hour increments of time from start and end date

james_butler — Wed, 31 Oct 2018 16:02:17 GMT

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

Re: Create 4 hour increments of time from start and end date

rwunderlich — Wed, 31 Oct 2018 16:31:19 GMT

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

-Rob

Re: Create 4 hour increments of time from start and end date

james_butler — Thu, 01 Nov 2018 13:31:11 GMT

Done

Using IterNo() to create hourly increments

Re: Create 4 hour increments of time from start and end date

james_butler — Thu, 01 Nov 2018 13:31:19 GMT

Thanks