How to calculate number of days between each status change per transaction as a field in the script?

[jblomqvist]

Hi all,

I have some transactions data table which looks like this:

Date	TransactionID	StatusName	Duration (This field to be created)
08-Mar-14	2001	Completed	7
08-Mar-14	9342	Completed	7
05-Mar-14	2001	Started	4
05-Mar-14	9342	Started	4
01-Mar-14	2001	Open	0
01-Mar-14	9342	Open	0

I would like to create the Duration field above in the script where it calculates the number of days it took between each status per transaction.

Does anyone know how I can do this please? A working code would be great

[sunny_talwar]

May be like this:

Table:

LOAD * INLINE [

    Date, TransactionID, StatusName

    08-Mar-14, 2001, Completed

    08-Mar-14, 9342, Completed

    05-Mar-14, 2001, Started

    05-Mar-14, 9342, Started

    01-Mar-14, 2001, Open

    01-Mar-14, 9342, Open

];

Left Join (Table)

LOAD TransactionID,

  Date as StartDate

Resident Table

Where StatusName = 'Open';

FinalTable:

LOAD *,

  Date - StartDate as Duration

Resident Table;

DROP Table Table;

[rwunderlich]

Another approach would be:

Final:

LOAD

  *

  ,if(TransactionID = Previous(TransactionID)

  ,(Date - Previous(Date)) + peek('Duration')

  ,0

  ) as Duration

Resident Table

Order By TransactionID, Date

;

DROP TABLE Table;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com