topic Re: Duration in QlikView

Duration

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Duration

sunny_talwar — Thu, 02 Feb 2017 15:46:06 GMT

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?

Re: Duration

MarcoWedel — Fri, 03 Feb 2017 00:01:51 GMT

Hi,

maybe something like

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

Re: Duration

Fri, 03 Feb 2017 04:42:41 GMT

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

Re: Duration

MarcoWedel — Fri, 03 Feb 2017 22:55:24 GMT

So you are looking for this?

Re: Duration

Clever_Anjos — Sat, 04 Feb 2017 00:12:15 GMT

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;

Re: Duration

maxgro — Sat, 04 Feb 2017 08:34:30 GMT

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

;

Re: Duration

MarcoWedel — Sat, 04 Feb 2017 18:59:45 GMT

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