I am having trouble finding a solution to excluding weekends from loading leave records by each day.
I have the following script:
[Deductions]:
LOAD
[Staff Member],
[First Name] AS [AA_LVE_Q.First Name],
[Preferred Full Name],
[Surname],
[Annual Leave Balance] AS [AA_LVE_Q.Annual Leave Balance],
[Leave to be Taken],
Date(Date#([As At Date], 'YYYYMMDD') ) AS [As At Date],
[Leave Type],
[Days Taken],
[Hours Taken]/[Days Taken] As [Hours Taken],
Date(Date#([Start Date], 'YYYYMMDD')+ IterNo() -1 ) AS [AA_LVE_Q.Start Date],
Date(Date#([End Date], 'YYYYMMDD') ) AS [AA_LVE_Q.End Date],
[Hours/Week],
[Entitlement Hours],
[Accrual Hours],
[LSL Balance],
[Purchase Leave],
[Sick Leave Balance],
[Branch] AS [AA_LVE_Q.Branch],
[Team/Unit] AS [AA_LVE_Q.Team/Unit],
[Employment Status] AS [AA_LVE_Q.Employment Status],
[Leave Group] AS [AA_LVE_Q.Leave Group]
FROM [lib://Qlik/AA_LVE_Q.CSV]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq, header is 1 lines)
While IterNo() <= [Days Taken];
Right now, I am able to breakdown the hours taken on a leave record evenly between two dates per day, however I need it to skip Saturday and Sunday in order to accurately report on this data.
Any thoughts?
