Data manipulating in table

[#1]

Hi

i have a table of job positions contating information of each time it got vacant and then filled , having both start date and end date.

PositionId	Start Date	End Date	Status
A	01.01.2003	30.06.2003	0pen
A	01.07.2003	15.12.2005	Filled
A	16.12.2005	04.05.2006	0pen
A	05.05.2006	31.12.9999	Filled

I wnat to calculate the time period of position from vacant to being filled each time or average time of any position to be filled

[perumal_41]

Hi

Use Peek or Previous Function  in Script

Regards

Perumal A

[#3]

can you apply it on my requirement plz

[perumal_41]

Hi,

Load * ,

if(Status='Filled',StartDate - Peek(EndDate) ) As No Days

From Table1;

use this Method.i thing Useful for U.

Regareds ,

Perumal A

[qliksus]

Permual method is right  but u have to add the order by function with

Position and Startdate so that the peek will work fine so try this

load

PositionId ,StartDate, EndDate, Status ,

if(PositionId = peek(PositionId) and  peek(Status) = '0pen', date(EndDate)- date(peek(StartDate))    ) as Diffdate

From Table

order by PositionId,  StartDate ;

[#6]

Hi
I have tried both suggestions but the calculated column is empty,
i am calculating the duration by this formula

Load
positionId,
[enddate]  - [startdate] As NoDays

where status='open'

from table

is it a ri ght approach??

because enddate of open status is just before the date it got filled

[perumal_41 · Accepted solution]

Hi

Try ur Method .little modify

Load
positionId,
([enddate] +1) - [startdate] As NoDays

where status='open'

from table

Regards

Perumal