topic Re: Calculating Aging between two or more rows in App Development

Calculating Aging between two or more rows

Tue, 16 Feb 2016 05:49:32 GMT

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

sequence	SKU	Date	Status
1	1	1/27/2014	Created
2	1	1/28/2014	Modify
3	1	1/29/2014	SUBMIT
4	1	2/2/2014	Closed
1	2	1/27/2014	Created
2	2	1/28/2014	Modify
3	2	1/29/2014	SUBMIT
1	3	2/2/2014	Created
2	3	2/2/2014	Modify
3	3	8/8/2014	SUBMIT
1	4	3/1/2014	Created
2	4	3/1/2014	Modify
1	5	8/8/2014	Created
2	5	8/8/2014	Modify
3	5	10/8/2014	SUBMIT
1	6	8/9/2014	Created
2	6	8/9/2014	Modify
3	6	10/8/2014	SUBMIT
4	6	11/11/2014	Closed

The target table should look like

sequence	SKU	Status	StartDate	EndDate	Aging
1	1	Created	1/27/2014	1/28/2014	1
2	1	Modify	1/28/2014	1/29/2014	1
3	1	SUBMIT	1/29/2014	2/2/2014	4
4	1	Closed	2/2/2014
1	2	Created	1/27/2014	1/28/2014	1
2	2	Modify	1/28/2014	1/29/2014	1
3	2	SUBMIT	1/29/2014
1	3	Created	2/2/2014	2/2/2014	0
2	3	Modify	2/2/2014	8/8/2014	187
3	3	SUBMIT	8/8/2014
1	4	Created	3/1/2014	3/1/2014	0
2	4	Modify	3/1/2014
1	5	Created	8/8/2014	8/8/2014	0
2	5	Modify	8/8/2014	10/8/2014	61
3	5	SUBMIT	10/8/2014
1	6	Created	8/9/2014	8/9/2014	0
2	6	Modify	8/9/2014	10/8/2014	60
3	6	SUBMIT	10/8/2014	11/11/2014	34
4	6	Closed	11/11/2014

Re: Calculating Aging between two or more rows

sunny_talwar — Tue, 16 Feb 2016 11:21:07 GMT

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;

Re: Calculating Aging between two or more rows

Gysbert_Wassenaar — Tue, 16 Feb 2016 11:23:27 GMT

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;