RaqForum 25 No.
294 View  • 
     ...

10.8 Get the date after n workdays

 
spl-cookbook(224)

Find the date after N workdays.
For each employee, find the number of cases when customer complaints are not solved within 10 workdays in the year 2014. Below is part of the source data:

ID CustomerID EmployeeId QuestionDate SolveDate
1 OLDWO 2 2014/01/01 2014/01/09
2 WELLI 7 2014/01/01 2014/01/07
3 LAUGB 2 2014/01/01 2014/01/07
4 LINOD 8 2014/01/02 2014/01/08
5 REGGC 5 2014/01/02 2014/01/12
… … … … …

workday(t,k,h) function finds the date k workdays before or after date t. Parameter h is a sequence of (non)holidays, whose members are either weekends or holidays. If they are weekends, treat them as shifted workdays.

SPL script:

A
1 =T(“AfterSale.csv”;“,”)
2 [2014/01/01,2014/01/26,2014/01/31,2014/02/03,2014/02/04,2014/02/05,2014/02/06,2014/02/08,2014/04/07,2014/05/01,2014/05/02,2014/05/04,2014/06/02,2014/09/08,2014/09/28,2014/10/01,2014/10/02,2014/10/03,2014/10/06,2014/10/07,2014/10/11]
3 =A1.select(year(QuestionDate)==2014 && workday(QuestionDate, 10, A2) < SolveDate)
4 =A3.groups(EmployeeId; count(~):Count)

A1 Import the AfterSale table.
A2 Define a sequence of holidays in the year 2014.
A3 Use workday() function to get the date after 10 workdays, excluding the holidays.
A4 Group records by employee and count the number of eligible cases.

Execution result:

EmployeeID Count
1 2
2 1
3 2
… …

 

spl-cookbook(224)
 
 •  294 View  •