Hi all, i'm fairly new to qlik and am a little stumped how to solve this problem:
I have a generic table of the sort
ID,INSTRUCTION, STATUSBEFORE, STATUSAFTER, TIMESTAMP, CHANGEDBY
1 456 DRAFT INSTRUCTED 01/01/2015 XYZ
2 456 INSTRUCTED SUBMITTED 03/01/2015 GHY
3 500 CHECKING 01/08/2016 KIO
4 500 CHECKING REJECTED 05/08/2016 LMK
5 500 REJECTED CHECKING 06/08/2016 JKH
6 500 CHECKING APPROVED 15/09/2015 HSH
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:
T_$(vTableName):
LOAD
%ID & %INSID AS %TID,//use this to sort by resident sort does not appear to work on multiple fields
%ID,
%INSID,
DDP_STATUS_HISTORY.BEFORE,
DDP_STATUS_HISTORY.AFTER,
DDP_STATUS_HISTORY.TIMESTAMP,
DDP_STATUS_HISTORY.CHANGEDBY
FROM
$(vDmQvdPath)$(vTableName)_final.qvd (QVD);
$(vTableName):
NOCONCATENATE
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...
ENDIF
