Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I want to count the number of working days in a month in a format that given below. I need to calculate this in the script.
Month Natural Day Working Day Total Working Days
jun 1 1 26
jun 2 2 26
jun 3 3 26
jun 4 3 26
jun 5 4 26
jun 6 5 26
jun 7 6 26
jun 8 7 26
jun 9 8 26
jun 10 9 26
jun 11 9 26
jul 12 10 26
Can anyone help me to find a solution for this problem?
Thank you
Stan
Hi Anil,
I have solved the issue by using the following script.
HolidayTmp:
LOAD Date
FROM
[.\Import\Holidays.xlsx]
(ooxml, embedded labels, table is Holidays);
ConcatTmp:
LOAD Concat (chr(39) & Date & chr(39),',') as HolidayDates
Resident HolidayTmp;
Let vPublicHolidays = FieldValue('HolidayDates',1);
Let vCurMonth = Month(Today());
Dates:
LOAD date(FieldValue('$(DateField)', RecNo())) AS TempDates
AUTOGENERATE FieldValueCount('$(DateField)')
;
Dates_Range:
LOAD
Max(TempDates) AS MaxDate,
Min(TempDates) AS MinDate
Resident Dates
;
Drop table Dates;
LET vDateMin = Num(Peek('MinDate', 0, 'Dates_Range'));
LET vDateMax = Num(Peek('MaxDate', 0, 'Dates_Range'));
DROP TABLE Dates_Range;
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)
;
Calendar:
LOAD
DateNumber AS $(DateField),
Date(DateNumber) AS Date,
Day(DateNumber) AS Day,
Weekday(DateNumber) AS WeekDay,
Week(DateNumber) AS Week,
Month(DateNumber) AS Month,
MonthName(DateNumber) AS MonthYear,
'Q' &Ceil(Month(DateNumber)/3) AS Quarter,
'S' &Ceil(Month(DateNumber)/6) AS Semester,
Year(DateNumber) AS Year,
If(WeekDay(Date(DateNumber)) <> 6 and not match(Date(DateNumber),$(vPublicHolidays)),1,0) as WorkingDayFlag
RESIDENT TempCalendar
ORDER BY DateNumber ASC
;
left join(Calendar)
Load
MonthYear,
sum(WorkingDayFlag) as TotalWorkingDays
resident Calendar group by MonthYear;
left join(Calendar)
Load
Date,
MonthYear,
if(MonthYear=peek(MonthYear), peek(WorkingDay) + WorkingDayFlag, WorkingDayFlag) as WorkingDay
resident Calendar
ORDER BY Date ASC
;
DROP TABLE TempCalendar,HolidayTmp;
Regards
Stan
Is this static or do we consider as Date filed
Hi Anil,
Date is a field in here.
Please consider the below attached .qvs file as a reference for master calendar script.
Thank You
Stan
Hi Anil,
I have solved the issue by using the following script.
HolidayTmp:
LOAD Date
FROM
[.\Import\Holidays.xlsx]
(ooxml, embedded labels, table is Holidays);
ConcatTmp:
LOAD Concat (chr(39) & Date & chr(39),',') as HolidayDates
Resident HolidayTmp;
Let vPublicHolidays = FieldValue('HolidayDates',1);
Let vCurMonth = Month(Today());
Dates:
LOAD date(FieldValue('$(DateField)', RecNo())) AS TempDates
AUTOGENERATE FieldValueCount('$(DateField)')
;
Dates_Range:
LOAD
Max(TempDates) AS MaxDate,
Min(TempDates) AS MinDate
Resident Dates
;
Drop table Dates;
LET vDateMin = Num(Peek('MinDate', 0, 'Dates_Range'));
LET vDateMax = Num(Peek('MaxDate', 0, 'Dates_Range'));
DROP TABLE Dates_Range;
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)
;
Calendar:
LOAD
DateNumber AS $(DateField),
Date(DateNumber) AS Date,
Day(DateNumber) AS Day,
Weekday(DateNumber) AS WeekDay,
Week(DateNumber) AS Week,
Month(DateNumber) AS Month,
MonthName(DateNumber) AS MonthYear,
'Q' &Ceil(Month(DateNumber)/3) AS Quarter,
'S' &Ceil(Month(DateNumber)/6) AS Semester,
Year(DateNumber) AS Year,
If(WeekDay(Date(DateNumber)) <> 6 and not match(Date(DateNumber),$(vPublicHolidays)),1,0) as WorkingDayFlag
RESIDENT TempCalendar
ORDER BY DateNumber ASC
;
left join(Calendar)
Load
MonthYear,
sum(WorkingDayFlag) as TotalWorkingDays
resident Calendar group by MonthYear;
left join(Calendar)
Load
Date,
MonthYear,
if(MonthYear=peek(MonthYear), peek(WorkingDay) + WorkingDayFlag, WorkingDayFlag) as WorkingDay
resident Calendar
ORDER BY Date ASC
;
DROP TABLE TempCalendar,HolidayTmp;
Regards
Stan