This lohs the status change of the Instruction. The status can change from any state top any other state there are not necessarily any business rules which define the life cycle of an instruction.
What I want to do is to workout the period in days, hours, minutes between the status changes and add that to a new filed called SLA.
So using the contrived data above the results would be
ID,INSTRUCTION, STATUSBEFORE, STATUSAFTER, TIMESTAMP, CHANGEDBY, SLA (NEW FIELD)
3 500 CHECKING 01/08/2016 KIO
4 500 CHECKING REJECTED 05/08/2016 LMK 4
5 500 REJECTED CHECKING 06/08/2016 JKH 1
6 500 CHECKING APPROVED 15/08/2016 HSH 9
I thought the easy way to do this would be to loop through a sorted table and check if the following record had the same instuction ID and if it did then simply calculate the the difference between the fields and then insert it back into the table. This seems to work though it appears there is no guarantee on what record the loop will start at and I also do not think we can write back to the table:
This is what I have up to know but I suspect there is a qlikview way of doing this which I;m missing:
%ID & %INSID AS %TID,//use this to sort by resident sort does not appear to work on multiple fields
LOAD *, NULL() AS SLA
RESIDENT T_$(vTableName) ORDER BY %TID ASC;
DROP TABLE T_$(vTableName);
LET vNumRows = NOOFROWS('$(vTableName)');
FOR i = 1 to $(vNumRows)// appears that even though this has sorted correcty internally when you evoke the loop it does not start at the smallest TID
LET vFirstIns = FIELDVALUE('%INSID',$(i));
LET vNextIns = FIELDVALUE('%INSID',$(i)+1);
IF $(vFirstIns) = $(vNextIns) THEN
LET vSLA = FIELDVALUE('DDP_STATUS_HISTORY.TIMESTAMP',$(i)+1) - FIELDVALUE('DDP_STATUS_HISTORY.TIMESTAMP',$(i));
// would update SLA with vSLA here.. if I could...