Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can anyone please suggest an efficient method for calculating the age of a transaction...
I have a Transaction activity table and I need to calculate the age of each and every transaction.
TransactionID | TactivityID | Status | CRT_TS |
T1 | T100 | Created | 03/01/16 |
T1 | T110 | Open | 03/25/16 |
T1 | T120 | Pended | 03/05/16 |
T1 | T120 | Reopened | 03/08/16 |
T1 | T130 | Closed | 04/10/16 |
T2 | T200 | Created | 02/01/16 |
T2 | T210 | Open | 03/25/16 |
T2 | T220 | Pended | 03/05/16 |
T2 | T230 | Closed | 04/15/16 |
T3 | T300 | Created | 04/01/16 |
T3 | T310 | Open | 04/02/16 |
T3 | T330 | Closed | 04/10/16 |
For a simple transition T3,
the age calculated by the networking days between CRT_TS of when it was opened and when it was closed.
e.g. - the age of Transaction T3 = NetWorkDays('04/01/2016',04/10/2016) = 6 Days
But the issue here is , if any one of the previous activity history is pended, then I need to deduct that many days from the total age.
e.g. For the transaction T1
NetWorkDays('03/01/2016','04/10/2016') = 32 Days
But the transaction was in pended state for 4 days (from 03/05/2016 to 03/08/2016). So the age of T1 would be 32- 4 = 28.
Any thoughts on how we can implement this logic efficiently.
The issue here is, I will have millions of records in the transaction activity table and I need to implement this in an efficient manner..
Appreciate any suggestions/thoughts on this.
Thanks
Nirmal.
Maybe something like
SET DateFormat = 'MM/DD/YYYY';
INPUT:
LOAD TransactionID,
TactivityID,
Status,
CRT_TS
FROM
[https://community.qlik.com/thread/213455]
(html, codepage is 1252, embedded labels, table is @1);
RESULT:
LOAD *,
If(previous(TransactionID)=TransactionID ,If(Status <> 'Pended', Networkdays(CRT_TS,Previous(CRT_TS)),-1),0) as Duration
Resident INPUT
ORDER BY TransactionID, CRT_TS desc;
DROP TABLE INPUT;
TransactionID | Sum(Duration) |
---|---|
74 | |
T1 | 28 |
T2 | 40 |
T3 | 6 |
Hi Nirmal,
You can consider using the generic load to get all stages/phases transposed. You can then do any calculation needed.
Please see attached for more details. I do see a slight difference in your T3 answer -- but that's because the networkdays function returns 29 (not 32 as mentioned in your post).
Thanks,
Camile
Sorry -- didn't see this response until after I posted. This works too. The numbers are slightly different than the example I crafted below. Namely, I got 55 for T2. I think the reason is because I used Created to calculate the difference instead of Open. Additionally in his data for T2 we have it listed as Pended, but hasn't been listed as "reopened". As a result T2 isn't calculating appropriately. Niram will need to evaluate his data to see what he wants to do in that circumstance.
The only limitations I see are:
1. If for some reason he has other CRT_TS values in his data -- that would need to be accounted for.
2. What happens in situations where he has a "pended" crt_ts value but no re-opened value? How does he want that calculated? (limitations with both methods).
In any case -- attached is with the change to Open (instead of Created as I had down below).