Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
luke1986
New Contributor III

Split duration time into seperate records per date

Hello,

I am trying to build a performance Indicator which should show the daily run time and the different breakdown reasons of the Machine.

The Problem is, that the running time sometimes start a day before the endtime.  So I need to do a cut at Midnight 00:00:00 and split the duration in the correct proportion to 2 diffrent records.

Here is the Example Data:

      

Notification_IDMachineStatusStart_Date_TimeEnd_Date_TimeDuration_Sec
112233ABCRunning10.02.2015 21:29:3911.02.2015 02:49:1419175
112235ABCRunning11.02.2015 02:49:1411.02.2015 02:59:57645
112238ABCRunning11.02.2015 02:59:5711.02.2015 03:09:25566
112240ABCRunning11.02.2015 03:09:2511.02.2015 03:17:14471
112258ABCRunning11.02.2015 03:17:1411.02.2015 05:28:117853
112260ABCRunning11.02.2015 05:28:1111.02.2015 05:28:110
112264ABCAdjusting11.02.2015 05:30:1811.02.2015 05:37:49444
112269ABCAdjusting11.02.2015 05:37:4911.02.2015 05:59:461324
112270ABCRunning11.02.2015 05:59:4611.02.2015 07:08:144105
112271ABCRunning11.02.2015 07:08:1412.02.2015 01:00:0064306

The outcome should look like this:

Notification_IDMachineStatusStart_Date_TimeEnd_Date_TimeDuration_SecDate for Calender
112233ABCRunning10.02.2015 21:29:3911.02.2015 00:00:00902110.02.2015
112233ABCRunning11.02.2015 00:00:0011.02.2015 02:49:141015411.02.2015
112235ABCRunning1.02.2015 02:49:1411.02.2015 02:59:5764511.02.2015
and so on...

Anyone has an idea how to code this?

Greetings Lukas

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Split duration time into seperate records per date

Temp:

Load

  Notification_ID & '|' & Machine as Key,

  Notification_ID,

  Machine,

  Status,

  TimeStamp(TimeStamp#(Start_Date_Time,'DD.MM.YYYY hh:mm:ss')) as Start_Date_Time,

  TimeStamp(TimeStamp#(End_Date_Time,'DD.MM.YYYY hh:mm:ss')) as End_Date_Time,

  Duration_Sec

Inline

[

  Notification_ID, Machine, Status, Start_Date_Time, End_Date_Time, Duration_Sec

  112233, ABC, Running, 10.02.2015 21:29:39, 11.02.2015 02:49:14, 19175

  112235, ABC, Running, 11.02.2015 02:49:14, 11.02.2015 02:59:57, 645

  112238, ABC, Running, 11.02.2015 02:59:57, 11.02.2015 03:09:25, 566

  112240, ABC, Running, 11.02.2015 03:09:25, 11.02.2015 03:17:14, 471

  112258, ABC, Running, 11.02.2015 03:17:14, 11.02.2015 05:28:11, 7853

  112260, ABC, Running, 11.02.2015 05:28:11, 11.02.2015 05:28:11, 0

  112264, ABC, Adjusting, 11.02.2015 05:30:18, 11.02.2015 05:37:49, 444

  112269, ABC, Adjusting, 11.02.2015 05:37:49, 11.02.2015 05:59:46, 1324

  112270, ABC, Running, 11.02.2015 05:59:46, 11.02.2015 07:08:14, 4105

  112271, ABC, Running, 11.02.2015 07:08:14, 12.02.2015 01:00:00, 64306

];

NoConcatenate

Final:

Load

  *,

  Interval(End_Date_Time-Start_Date_Time,'ss') as Duration_Sec,

  Date(Floor(Start_Date_Time)) as [Date For Calendar];

Load

  Key

  ,Notification_ID

  ,Machine

  ,Status

  ,If(Key = Peek(Key), TimeStamp(Floor(Start_Date_Time) + IterNo() - 1), Start_Date_Time) as Start_Date_Time

  ,If(Floor(Start_Date_Time) + IterNo() < End_Date_Time, TimeStamp(Floor(Start_Date_Time) + IterNo()), End_Date_Time) as End_Date_Time

Resident Temp

While Floor(Start_Date_Time) + IterNo() - 1 < End_Date_Time;

Drop Table Temp;

Drop Field Key;

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: Split duration time into seperate records per date

Temp:

Load

  Notification_ID & '|' & Machine as Key,

  Notification_ID,

  Machine,

  Status,

  TimeStamp(TimeStamp#(Start_Date_Time,'DD.MM.YYYY hh:mm:ss')) as Start_Date_Time,

  TimeStamp(TimeStamp#(End_Date_Time,'DD.MM.YYYY hh:mm:ss')) as End_Date_Time,

  Duration_Sec

Inline

[

  Notification_ID, Machine, Status, Start_Date_Time, End_Date_Time, Duration_Sec

  112233, ABC, Running, 10.02.2015 21:29:39, 11.02.2015 02:49:14, 19175

  112235, ABC, Running, 11.02.2015 02:49:14, 11.02.2015 02:59:57, 645

  112238, ABC, Running, 11.02.2015 02:59:57, 11.02.2015 03:09:25, 566

  112240, ABC, Running, 11.02.2015 03:09:25, 11.02.2015 03:17:14, 471

  112258, ABC, Running, 11.02.2015 03:17:14, 11.02.2015 05:28:11, 7853

  112260, ABC, Running, 11.02.2015 05:28:11, 11.02.2015 05:28:11, 0

  112264, ABC, Adjusting, 11.02.2015 05:30:18, 11.02.2015 05:37:49, 444

  112269, ABC, Adjusting, 11.02.2015 05:37:49, 11.02.2015 05:59:46, 1324

  112270, ABC, Running, 11.02.2015 05:59:46, 11.02.2015 07:08:14, 4105

  112271, ABC, Running, 11.02.2015 07:08:14, 12.02.2015 01:00:00, 64306

];

NoConcatenate

Final:

Load

  *,

  Interval(End_Date_Time-Start_Date_Time,'ss') as Duration_Sec,

  Date(Floor(Start_Date_Time)) as [Date For Calendar];

Load

  Key

  ,Notification_ID

  ,Machine

  ,Status

  ,If(Key = Peek(Key), TimeStamp(Floor(Start_Date_Time) + IterNo() - 1), Start_Date_Time) as Start_Date_Time

  ,If(Floor(Start_Date_Time) + IterNo() < End_Date_Time, TimeStamp(Floor(Start_Date_Time) + IterNo()), End_Date_Time) as End_Date_Time

Resident Temp

While Floor(Start_Date_Time) + IterNo() - 1 < End_Date_Time;

Drop Table Temp;

Drop Field Key;

View solution in original post

Highlighted
luke1986
New Contributor III

Re: Split duration time into seperate records per date

Thank you Manish, that worked!