Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor II

Data manipulating in table

Hi

Try ur Method .little modify

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

where status='open'

from table

Regards

Perumal

 

6 Replies
perumal_41
Valued Contributor II

Data manipulating in table

Hi

Use Peek or Previous Function  in Script

Regards

Perumal A

Not applicable

Data manipulating in table

can you apply it on my requirement plz

perumal_41
Valued Contributor II

Data manipulating in table

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
Valued Contributor

Data manipulating in table

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

Data manipulating in table

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
Valued Contributor II

Data manipulating in table

Hi

Try ur Method .little modify

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

where status='open'

from table

Regards

Perumal

 

Community Browser