Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Calculating Aging between two or more rows

I have a table like this in Qlik - This is a resident table

Can anyone help me that how can calculate Aging between two status for each ticket_no

How can I write this load script ?? If I have a same table in DB then I know how to write it ...

but now this table is generated from qlik load

sequenceSKU DateStatus
111/27/2014Created
211/28/2014Modify
311/29/2014SUBMIT
412/2/2014Closed
121/27/2014Created
221/28/2014Modify
321/29/2014SUBMIT
132/2/2014Created
232/2/2014Modify
338/8/2014SUBMIT
143/1/2014Created
243/1/2014Modify
158/8/2014Created
258/8/2014Modify
3510/8/2014SUBMIT
168/9/2014Created
268/9/2014Modify
3610/8/2014SUBMIT
4611/11/2014Closed

The target table should look like

    

sequenceSKU StatusStartDateEndDateAging
11Created1/27/20141/28/20141
21Modify1/28/20141/29/20141
31SUBMIT1/29/20142/2/20144
41Closed2/2/2014
12Created1/27/20141/28/20141
22Modify1/28/20141/29/20141
32SUBMIT1/29/2014
13Created2/2/20142/2/20140
23Modify2/2/20148/8/2014187
33SUBMIT8/8/2014
14Created3/1/20143/1/20140
24Modify3/1/2014
15Created8/8/20148/8/20140
25Modify8/8/201410/8/201461
35SUBMIT10/8/2014
16Created8/9/20148/9/20140
26Modify8/9/201410/8/201460
36SUBMIT10/8/201411/11/201434
46Closed11/11/2014
2 Replies
MVP
MVP

Re: Calculating Aging between two or more rows

Try this:

Table:

LOAD sequence,

     SKU,

     Date,

     Status

FROM

[https://community.qlik.com/thread/205682]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  (StartDate - EndDate) as Aging;

LOAD sequence,

     SKU,

     Date as StartDate,

     Status,

  If(Peek('SKU') = SKU, Peek('StartDate')) as EndDate

Resident Table

Order By SKU, Date;

DROP Table Table;


Capture.PNG

Re: Calculating Aging between two or more rows

Something like this:

Temp:

LOAD

     sequence,

     SKU,

     Status,

     StartDate

FROM

     ...source...

     ;

LOAD

     sequence,

     SKU,

     Status,

     Date as StartDate,

     If(Previous(SKU)=SKU,Previous(Date)) as EndDate,

     If(Previous(SKU)=SKU,Previous(Date)) - Date as Aging

RESIDENT

     Temp

ORDER BY

     SKU,

     sequence

     ;


DROP TABLE Temp;


talk is cheap, supply exceeds demand
Community Browser