Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor II

## Split Start Date and End Date into Hours Per Days

Hello,

@MK_QSL << Hi Manish adding you as I have seen you have answered a similar before. Hoping you can help this time too! 😄

I have a table which has the following structure;

 Work Center Name Start Date Stop Date AA 2020-06-09 19:34:11.34113411 2020-06-10 09:29:45.29452945

And for each entry I would like to get this,

 Work Center Name Date Duration (Hours) AA 09/06/2020 3.5 AA 10/06/2020 9.5

I have tried various approaches - this is my latest;

Name as [Facility],
timestamp(StartDate) as [StartDate],
timestamp(StopDate) as [StopDate];

NoConcatenate

[Downtime]:
*,

Interval(StopDate-StartDate,'HH:mm:ss') as DurationHours,

Date(Floor(StartDate)) as [Date];

Facility
,If(Facility = Peek(Facility), TimeStamp(StartDate + IterNo() - 1), StartDate) as StartDate
,If(StartDate + IterNo() < StopDate, TimeStamp(StartDate + IterNo()), StopDate) as StopDate
Resident Temp

While Floor(StartDate) + IterNo() - 1 < StopDate;

Drop Table Temp;

The trouble is with this is it just seems to assign all the time to the start date rather than splitting it over the start and end date.

If anyone could help me I would be very appreciative.

Thank you,

Mike

Labels (3)

• ### Script

1 Solution

Accepted Solutions
MVP

Try this

``````Table:
LOAD [Work Center Name],
Date(Floor([Start Date]) + IterNo() - 1) as Date,
If(Floor([Start Date]) = Floor([Stop Date]), Round(Frac([Stop Date]) - Frac([Start Date]), 1/48) * 24,
If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), Round(1 - Frac([Start Date]), 1/48) * 24,
If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), Round(Frac([Stop Date]), 1/48) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
Work Center Name, Start Date, Stop Date
AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];``````

and then you can just use Work Center Name and Date as dimension and Sum(Duration) as Measure. Also, not sure if you need rounding or not, but feel free to remove it if you think it is not needed.

``````Table:
LOAD [Work Center Name],
Date(Floor([Start Date]) + IterNo() - 1) as Date,
If(Floor([Start Date]) = Floor([Stop Date]), (Frac([Stop Date]) - Frac([Start Date])) * 24,
If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), (1 - Frac([Start Date])) * 24,
If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), (Frac([Stop Date])) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
Work Center Name, Start Date, Stop Date
AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];``````
7 Replies

@mgDev  how it is 3.5 hours for  09/06/2020?

MVP

Try this

``````Table:
LOAD [Work Center Name],
Date(Floor([Start Date]) + IterNo() - 1) as Date,
If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), Round(1 - Frac([Start Date]), 1/48) * 24,
If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), Round(Frac([Stop Date]), 1/48) * 24, 24)) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
Work Center Name, Start Date, Stop Date
AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
AB, 2020-06-09 19:34:11.34113411, 2020-06-15 09:29:45.29452945
];``````
Contributor II
Author

Hi Kush,

Apologies my mistake - should approx be 4.5

Thanks,

Mike

MVP

Did you try the script provided below? I believe it should work

Contributor II
Author

Hi Sunny,

Thank you so much for coming back to me - unfortunately this isn't working for my data set. This is the output from this query;

 Name Date Duration AA 09/06/2020 4.5 AA 09/06/2020 9 AA 10/06/2020 1.5 AA 10/06/2020 9.5 AA 10/06/2020 11.5 AA2 09/06/2020 9.5 AA2 10/06/2020 9.5 AA2 10/06/2020 18

This is the source data

 Work Center Name Start Date Stop Date AA 2020-06-09 15:13:41.13411341 2020-06-09 17:55:25.55255525 AA 2020-06-09 19:34:11.34113411 2020-06-10 09:29:45.29452945 AA 2020-06-10 12:21:23.21232123 2020-06-10 12:32:53.32533253 AA 2020-06-10 22:19:54.19541954 2020-06-11 06:05:45.545545 AA2 2020-06-09 14:42:09.429429 2020-06-09 15:17:56.17561756 AA2 2020-06-10 06:04:27.427427 2020-06-10 08:03:24.324324

What I think I should be getting is something more akin to the approximate numbers below;

 Work Center Name Date Duration (Approx) AA 09/06/2020 7.12 AA 10/06/2020 11.40 AA 11/06/2020 6.00 AA2 09/06/2020 0.50 AA2 10/06/2020 2.00

Thank you for your help,

Mike

MVP

Try this

``````Table:
LOAD [Work Center Name],
Date(Floor([Start Date]) + IterNo() - 1) as Date,
If(Floor([Start Date]) = Floor([Stop Date]), Round(Frac([Stop Date]) - Frac([Start Date]), 1/48) * 24,
If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), Round(1 - Frac([Start Date]), 1/48) * 24,
If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), Round(Frac([Stop Date]), 1/48) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
Work Center Name, Start Date, Stop Date
AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];``````

and then you can just use Work Center Name and Date as dimension and Sum(Duration) as Measure. Also, not sure if you need rounding or not, but feel free to remove it if you think it is not needed.

``````Table:
LOAD [Work Center Name],
Date(Floor([Start Date]) + IterNo() - 1) as Date,
If(Floor([Start Date]) = Floor([Stop Date]), (Frac([Stop Date]) - Frac([Start Date])) * 24,
If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), (1 - Frac([Start Date])) * 24,
If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), (Frac([Stop Date])) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
Work Center Name, Start Date, Stop Date
AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];``````
Contributor II
Author

Hi Sunny,

This is working perfectly - thank you soo much! Really appreciated.

Thanks again,

Mike

Tags
Community Browser