Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have some transactions data table which looks like this:
Date | TransactionID | StatusName | Duration (This field to be created) |
08-Mar-14 | 2001 | Completed | 7 |
08-Mar-14 | 9342 | Completed | 7 |
05-Mar-14 | 2001 | Started | 4 |
05-Mar-14 | 9342 | Started | 4 |
01-Mar-14 | 2001 | Open | 0 |
01-Mar-14 | 9342 | Open | 0 |
I would like to create the Duration field above in the script where it calculates the number of days it took between each status per transaction.
Does anyone know how I can do this please? A working code would be great
May be like this:
Table:
LOAD * INLINE [
Date, TransactionID, StatusName
08-Mar-14, 2001, Completed
08-Mar-14, 9342, Completed
05-Mar-14, 2001, Started
05-Mar-14, 9342, Started
01-Mar-14, 2001, Open
01-Mar-14, 9342, Open
];
Left Join (Table)
LOAD TransactionID,
Date as StartDate
Resident Table
Where StatusName = 'Open';
FinalTable:
LOAD *,
Date - StartDate as Duration
Resident Table;
DROP Table Table;
Another approach would be:
Final:
LOAD
*
,if(TransactionID = Previous(TransactionID)
,(Date - Previous(Date)) + peek('Duration')
,0
) as Duration
Resident Table
Order By TransactionID, Date
;
DROP TABLE Table;
-Rob