Calculating the time lapsed between alternative entries for the same field

[#1]

Hi - I have two fields. The first being Status which can be "open" or "closed" and the second being Date. Where a entry has both an open and a closed date I would like to calculate the time lapsed between the two. Any answers gratefully received.

Many thanks

Rhys

[Miguel_Angel_Baeyens]

Hi Rhys,

Are both dates (open and closed date) in the same row? If not, you may do a two step load, the first from the data source the second RESIDENT ORDER BY number of unique key, then use the Previous() and Peek() functions to get the corresponding date for the open (or close) value and calculate the interval.

Probably easier, you can also do a mapping load of the key field and the close date when the status is Open, then substract the result of the ApplyMap() function from the Closed date field when the status is Closed.

Hope that helps.

Miguel

[magavi_framsteg]

LOAD

     EntryID

     ,Status

     ,Date

     ,if (peek('EntryID') = EntryID AND peek('Status') <> Status, (Date - peek('Date')), null()) as TimeLapse

ORDER BY

     EntryID ASC

     ,Status DESC

;

Hope this helps.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[#4]

Hi thanks for both of these it maybe I'm inputting the script wrong but I can't make them work.

The data is structured as

Fee Earner     Description     Status     Date

x                    1                    Open     dd-mon-yy

x                    1                    Closed     dd-mon-yy

y                    1                    Open     dd-mon-yy etc

I want to work out the time elapsed between the open and closed date?

[magavi_framsteg]

Hi again.

Yes, you should be able to run the code with a bit of changes.

LOAD
     [Fee Earner]
     ,Status
     ,Date(Date) as Date
     ,if (peek('[Fee Earner]') = [Fee Earner] AND peek('Status') <> Status, num(Date) - num(peek('Date')), null()) as DaysBetweenDates
ORDER BY
     [Fee Earner] ASC
     ,Status DESC
;

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[#6]

Hi Magnus this doesn't appear to work.

The link is the description field which is free text. There is only ever a maximum of two identical entries, under status these are closed or open. The date field is the only time element.

[magavi_framsteg]

Hi.

What I suggested is sort of pseudo code to show the concept of peek.

The proposed solution checks previous row on each row and checks (with peek) if it's the same "key" and if status has changed since previous row.

Another way is like Miguel proposed;

First load closed status orders as tabClosed. Then when you load opened status orders you can check against tabClosed, if match then calculate the time.

Or join them so you have two dates for keys that have both statuses.

I'm sorry for the lack of examples, I'm on the subway and I hope you got a kick in the right direction, there's always so many ways of getting across a river

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.