topic Re: Splitting a single date range row into many row according to working day and Holidays in QlikView

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

enricocamerin — Thu, 25 Jan 2018 10:30:27 GMT

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;

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

sunny_talwar — Thu, 25 Jan 2018 16:51:54 GMT

Where do you keep the special days (holidays)?

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

sunny_talwar — Thu, 25 Jan 2018 16:57:41 GMT

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;

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

enricocamerin — Mon, 29 Jan 2018 13:35:20 GMT

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;

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

sunny_talwar — Mon, 29 Jan 2018 13:42:34 GMT

Super awesome