Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;