Duration

[#1]

Hi All,

I have a data set where I am required to calculate the duration of a product that has been 'Live' in hh:mm:ss

Date	Product	Status
10-08-2015 10:12:10 AM	W	Live
10-08-2015 05:40:25 PM	W	Live
10-08-2015 07:28:19 PM	W	Dead
10-08-2015 09:30:00 PM	W	Live
10-08-2015 11:02:34 PM	W	Live
11-08-2015 06:00:00 AM	D	Live
11-08-2015 04:20:55 PM	D	Live

[sunny_talwar]

Can you share what your required output needs to look like from the sample you have shared? Also, do you want this to be done in script or front end?

[MarcoWedel]

Hi,

maybe something like

Product	Duration
W	12:50:24
D	11:20:55

[#4]

Hi,

Needs to be done in the backend. The desired output would be like :

Product	Duration (hh:mm:ss)
W	09 : 16 : 09
D	10 : 20 : 55

Live duration will be counted as the difference between the first Live reading till a Dead reading is encountered. So the output above is the exact fig.

Thanks

[MarcoWedel]

So you are looking for this?

[Clever_Anjos]

Something like this?

Base:
LOAD
    Timestamp#(Date, 'MM-DD-YYYY hh:mm:ss TT') as Date,
    Product,
    Status
FROM [https://community.qlik.com/thread/248451](html, codepage is 1252, embedded labels, table is @1);
LOAD
      Date,
      If(Product = Peek('Product'),Peek('Date'),null())  as Date2,
      Product,
      Status
Resident Base
Order by  Product, Date DESC; 
Drop Table Base;

[maxgro]

Result

Script: I added some test data

Base: 

LOAD 

    Timestamp(Timestamp#(Date, 'DD-MM-YYYY hh:mm:ss TT')) as Date, 

    Product, 

    Status 

FROM [https://community.qlik.com/thread/248451](html, codepage is 1252, embedded labels, table is @1); 

// -------------- comment this, just for test, start

Concatenate (Base)

load

    Timestamp(Timestamp#(Date, 'DD-MM-YYYY hh:mm:ss TT')) as Date, 

    Product, 

    Status 

inline [

Date, Product, Status 

10-08-2016 10:12:10 AM, W, Live

10-08-2016 05:40:25 PM, W, Live

10-08-2016 07:28:19 PM, W, Dead

10-08-2016 09:30:00 PM, W, Live

10-08-2016 11:02:34 PM, W, Live

11-08-2016 06:00:00 AM, D1, Dead

11-08-2016 04:20:55 PM, D1, Live

12-08-2016 06:00:00 AM, D1, Live

12-08-2016 04:20:55 PM, D1, Live

];

// ------------------ comment this, just for test, end

Base2:

LOAD 

  Date, 

    If(Product = Peek('Product') and (Peek('Status')='Dead' or Peek('StatusNew')='Dead'), 'Dead',

    If(not (Product = Peek('Product')), Status,

    Alt(Peek('StatusNew'), 'Live')

    )) as StatusNew,

    Product, 

    Status

Resident Base 

Order by  Product, Date;

Drop Table Base;

Result:

LOAD

  Product,

  Interval(MaxDate - MinDate) as Duration,

  MinDate, MaxDate

;

LOAD

  Product,

  min(Date) as MinDate,

  max(Date) as MaxDate

Resident Base2

Where StatusNew = 'Live'

Group By Product

;

[MarcoWedel]

using this script:

tabProdStat:
LOAD * FROM [https://community.qlik.com/thread/248451] (html, codepage is 1252, embedded labels, table is @1);

tabProdDur:
LOAD Product,
     Interval(Min(If(Status='Dead',Date))-Min(If(Status='Live',Date))) as Duration
Resident tabProdStat
Group By Product;

hope this helps

regards

Marco