Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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
];
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;
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
Great post, thank you!
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
It would be good to start a new thread with some sample data.
-Rob
Thanks