Getting days between status changes from generic table

Anonymous — Mon, 04 Apr 2016 11:40:25 GMT

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

Re: Getting days between status changes from generic table

marcus_sommer — Mon, 04 Apr 2016 13:03:02 GMT

Within an on Instruction and Timestamp sorted resident load you could check with peek() or previous() the previous record and calculate with them so that you with:

if(INSTRCUTION = previous(INSTRUCTION), TIMESTAMP - previous(TIMESTAMP) , 0) as TIMEDIFFERENCE

get your time-difference. A very good explanation could you find here: Peek() or Previous() ?

- Marcus

Re: Getting days between status changes from generic table

Anonymous — Mon, 04 Apr 2016 14:42:35 GMT

Thanks so much easier when you know how

topic Re: Getting days between status changes from generic table in QlikView

Getting days between status changes from generic table

Re: Getting days between status changes from generic table

Re: Getting days between status changes from generic table