Skip weekends when loading leave period by day

[Anonymous]

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?

[luismadriz]

Hi Angelo,

Is seems a bit confusing without more functional understanding of your design. If each record is the leave taken then you shouldn't prevent any record from going in, but that just reflects my lack of understanding of your design. Antoher thing, the expression While IterNo() <= [Days Taken] is what confused me the most!

Can you elaborate on what each record is about?

I imagine you need to exclude weekends and holidays for which you may want to take a look at this function: networkdays

I hope this helps,

Cheers,

Luis

[Anonymous]

No problems Luis,

I'll give you more context on this:

I basically have a leave report with a Start and End date with the days taken and the hours taken for that record.

What I have loaded so far is the record by the number of days taken for each record, splitting the hours taken evenly across those days.

The start date increments per days given on each record.

For example:

Start date: 19/6/2017

End date: 30/6/2017

The amount of hours taken that period was 70 hours over the course of 10 days (2weeks).

It proceeds to load the data line by line and listing 7 hours for each day but ends on 28/6/2017 rather than 30/6/2017 which is what i require.

I'm trying to skip Saturday 24/6 and Sunday 25/6 in order for this leave record to be loaded correctly.

[luismadriz]

Hi Angelo,

Without attempting to change your logic I can think of loading the data for 12 days instead of 10, and then do another pass or load just to remove the weekends

For example use while iterno() <= enddate-startdate+1 instead o <= days taken....

The second pass will be a noconcatenate load where weekday is not saturday or sunday... something like that...

If I think of something to do it on one pass, I'll let you know,

I hope this helps,

Cheers,

Luis

[luismadriz]

and of course drop the initial table...

Cheers,

Luis

[Anonymous]

Hi Luis,

i tried to load the end-date-start date piece but it was loading >1mil records which didn't seem right.

Which table were you referring to drop?

A

[luismadriz]

Maybe you need to make sure the dates are recognised properly and end-start+1 is giving you a reasonable value.

Can you please show me a snapshot of the data when you use While IterNo() <= [Days Taken];

Luis

[Anonymous]

I think I finally got what I wanted to achieve:

Disregard the days taken column.

The start date finally excludes Sat and Sunday.

Frustrating bit is that eventually I would need to figure out public holidays but at least this is a start.

Basically took some trial and error but finally got there, even though this probably isn't the best way of doing it. Here's the code:

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],

    if(WeekDay(Date(Date#([Start Date], 'YYYYMMDD')+ IterNo() -1))='Sat.',
    Date(Date#([Start Date], 'YYYYMMDD')+ IterNo() -2),
   
    if(WeekDay(Date(Date#([Start Date], 'YYYYMMDD')+ IterNo() -1))='Sun.',
    Date(Date#([Start Date], 'YYYYMMDD')+ IterNo() -3),
   
   
    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() <= [End Date]-[Start Date]+1;

I added the IFs statements to help and I did end up taking your advice on the While statement.

Thanks again

cheers

Angelo

[luismadriz · Accepted solution]

Hi Angelo,

This example may help clarify:

Initial:

Load * inline [

Staff,Start,End,Others

1,19/6/2017,30/6/2017,X

2,15/9/2017,27/9/2017,Y];

FirstPass:

Load Staff,

     Start,

     End,

     Others,

     Date(Start+IterNo()-1) as DayStart,

     WeekDay(Date(Start+IterNo()-1),0) as WeekDayStart,

     Num(WeekDay(Date(Start+IterNo()-1),0)) as NumWeekDayStart

Resident Initial

While IterNo() <= Date(End)-Date(Start)+1;

SecondPass:

NoConcatenate

Load *

Resident FirstPass

Where NumWeekDayStart <= 4;

Drop Tables Initial, FirstPass;

[luismadriz]

Hi Angelo,

I'm glad the recommendation helped, however your design is still not quite there yet because you've added the 12 records. It may look that you haven't but you've added Friday 3 times in AA_LVE_Q.Start Date

Please at least mark my responses that helped you,

Good luck,

Luis