Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am looking for some assistance, I need to calculate the Working days by Employee excluding their Leave and Public Holidays. I have attempted this via the script below. This issue is, it is not excluding their leave from the NetWorkDays calculation (ActualDays). I am wondering if this is an aggregation issue as each Employee has a different list of holidays? If I paste a string in directly for one Employee it removes the holidays but using the HD field appears to do nothing?
Holidays:
LOAD
Consultant,
Concat(chr(39) & "Leave Bank Holiday Dates" &chr(39),',') as HD
FROM [lib://QVD_Test/Timesheet_HolidayDates.qvd]
(qvd)
Group By Consultant
;
WorkDates:
LOAD
RTrim(Capitalize("Name of Resource")) as Consultant,
"PO Start date",
"PO End Date"
FROM [lib://PO Data/PO Source Template.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(WorkDates)
LOAD * Resident Holidays;
DROP Table Holidays;
Final:
LOAD
Consultant,
NetWorkDays([PO Start date],[PO End Date], HD) as ActualDays
Resident WorkDates;
DROP Table WorkDates;
Many thanks,
Nikki
Yeah, HD is just one string and therefore one argument. It's not a comma separated list of arguments you can pass to the NetWorkDays function that way.
But you can calculate per employee a sum of his/her holiday days that are not weekend days. You can then later subtract this number from the other working days:
Holidays:
LOAD
Consultant,
Sum(NetWorkDays([Leave Bank Holiday Dates],[Leave Bank Holiday Dates])) as HD
FROM [lib://QVD_Test/Timesheet_HolidayDates.qvd]
(qvd)
Group By Consultant;
WorkDates:
LOAD
RTrim(Capitalize("Name of Resource")) as Consultant,
"PO Start date",
"PO End Date"
FROM [lib://PO Data/PO Source Template.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(WorkDates)
LOAD * Resident Holidays;
DROP Table Holidays;
Final:
LOAD
Consultant,
NetWorkDays([PO Start date],[PO End Date]) - HD as ActualDays
Resident WorkDates;
DROP Table WorkDates;