Splitting a single date range row into many row according to working day and Holidays

[enricocamerin]

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

inner join (expanded)

load cod,  nr_day

resident work_package_period;

drop table work_package_period;

exit Script;

[sunny_talwar]

Where do you keep the special days (holidays)?

[sunny_talwar]

For Excluding Sat and Sun

range:

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 *

Where Not Match(WeekDay(xStartDate), 'Sun', 'Sat');

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

Inner Join (expanded)

LOAD cod,

nr_day

Resident work_package_period;

DROP Table work_package_period;

EXIT SCRIPT;

[enricocamerin]

Thanks Sunny for your Help

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

inner join (expanded)

load cod,  nr_day

resident work_package_period;

drop table work_package_period;

[sunny_talwar]

Super awesome