Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data manipulating in table

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 DateStatus
A01.01.200330.06.20030pen
A01.07.200315.12.2005Filled
A16.12.200504.05.20060pen
A05.05.200631.12.9999Filled

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

1 Solution

Accepted Solutions
perumal_41
Partner - Specialist II
Partner - Specialist II

Hi

Try ur Method .little modify

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

where status='open'

from table

Regards

Perumal

 

View solution in original post

6 Replies
perumal_41
Partner - Specialist II
Partner - Specialist II

Hi

Use Peek or Previous Function  in Script

Regards

Perumal A

Not applicable
Author

can you apply it on my requirement plz

perumal_41
Partner - Specialist II
Partner - Specialist II

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
Specialist II
Specialist II

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 ;

Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

Hi

Try ur Method .little modify

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

where status='open'

from table

Regards

Perumal