Splitting a single date range row into many row according to working day and Holidays
Dears,
I have this Range table and I need to create a single row for each working day within the StartDate and EndDate.
The amount have to be diveded by the number working day found in the Range.
I have this script which work great in a normal calendar, but I'm not able to adapt it in order to get only the working day ( without Saturday, Sunday and special days).
thanks in Advance for your Help
Enrico
Script
load * inline [
cod, StartDate, EndDate, amount
'a', '01-02-2018', '30-04-2018', 8000
'b', '01-03-2018', '30-06-2018', 4000
'c', '01-06-2018', '31-07-2018', 12000
];
expanded:
LOAD
cod, iterno() as nr,
date(RangeMax(StartDate, StartDate+ iterno()-1)) as xStartDate,
date(RangeMin(EndDate, StartDate+ iterno()-1)) as xEndDate,
amount as amount_expanded
RESIDENT range
WHILE (StartDate+ iterno()-1) <= EndDate ;
work_package_period:
load cod, max(nr)as nr_day
Resident expanded
group by cod;
/// comment nr_day is the use in report to dived the amount
I then decided to join the MasterCalendar where a added a field for the Holiday
Then to calculate the number of iteration I inserted a 1 number inside the while routine and then I sum up this number. This variable is used to assign the 'amount' to each number
Script
load * inline [
cod, StartDate, EndDate, amount
'a', '01-02-2018', '30-04-2018', 8000
'b', '01-03-2018', '30-06-2018', 4000
'c', '01-06-2018', '31-07-2018', 12000
];
expanded:
LOAD
cod,
1 as nr,
date(RangeMax(StartDate, StartDate+ iterno()-1)) as xStartDate,
date(RangeMin(EndDate, StartDate+ iterno()-1)) as xEndDate,
amount as amount_expanded
RESIDENT range
WHILE (StartDate+ iterno()-1) <= EndDate ;
inner join (expanded)
load distinct date as xStartDate
Resident MasterCalendar
where weekend = 0 and holiday =0;
work_package_period:
load cod, sum(nr) as nr_day
Resident expanded
group by cod;
/// comment nr_day is the use in report to dived the amount
