Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
darren_hankinso
Contributor II
Contributor II

Getting days between status changes from generic table

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

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

darren_hankinso
Contributor II
Contributor II
Author

Thanks so much easier  when you know how