Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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;

2 Replies
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;

luke1986
New Contributor III

Re: Split duration time into seperate records per date

Thank you Manish, that worked!

Community Browser