topic Re: Scripting - Check Last Working Day in QlikView

Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:09:15 GMT

Hi,

I have a challenge and I'd like to know if there's a elegant solution.

My Script loads a calendar and then applies a flag for working days (i.e. Monday - Friday less and Public Holidays). So I end up with something like this:

Date ,Working Day Flag

3-Apr-2014 ,1

4-Apr-2014 ,1

5-Apr-2014 ,0

6-Apr-2014 ,0

7-Apr-2014 ,1

8-Apr-2014 ,1

I want to add another column [Last Working Day] which gives the values of the last date where the [Working Day Flag] was 1 or the [Date] value if it is a working day. For example:

Date ,Working Day Flag ,Last Working Day

3-Apr-2014 ,1 ,3-Apr-2014

4-Apr-2014 ,1 ,4-Apr-2014

5-Apr-2014 ,0 ,4-Apr-2014

6-Apr-2014 ,0 ,4-Apr-2014

7-Apr-2014 ,1 ,7-Apr-2014

8-Apr-2014 ,1 ,8-Apr-2014

I can do this using previous() although I want it to be able to work where there are several consecutive none working days, as Previous() is limited to three iterations I'd like to know if there's another technique for this.

Many thanks

Richard

Re: Scripting - Check Last Working Day

SunilChauhan — Tue, 22 Apr 2014 10:15:00 GMT

If(Date=Monthend(Date) and weekday(Date)<>'Sat' and weekday(Date)='Sun',Date) as last working day

Re: Scripting - Check Last Working Day

lironbaram — Tue, 22 Apr 2014 10:18:37 GMT

as far as i know previous is not limited so if your expression will look like this

if([Working Day Flag] =1 ,Date,previous([Last Working Day])) AS Last Working Day

should work for as many dates as needed

Re: Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:24:13 GMT

Hi Liron,

Thanks for your response, that was an approach I was taking. The issue being you first need to create the field [Last Working Day] in order to reference it.

So, looking at some simple code it won't work (unless I'm miss interpreting your answer?)

Load * Inline[

Date                         ,Working Day Flag

3-Apr-2014               ,1

4-Apr-2014               ,1

5-Apr-2014               ,0

6-Apr-2014               ,0

7-Apr-2014               ,1

8-Apr-2014               ,1

];

Calendar:

Load

Date

,[Working Day Flag]

,if([Working Day Flag] =1 ,Date,previous([Last Working Day])) AS [Last Working Day]

Resident Data;

Drop Table Data;

Re: Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:27:02 GMT

My understanding is you're limited to three nested Previous() statement

I did try using Date as the field referenced above and nesting If and Previous statements. Works for three days which would cover a standard weekend but I got an error when I tried:

Previous(Previous(Previous(Previous(Date))))

Re: Re: Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:44:38 GMT

Thanks for your response Sunil,

Your reply has me a bit lost, the condition Date=Monthend(Date) means it would only evaluate Monthends?

Re: Scripting - Check Last Working Day

anbu1984 — Tue, 22 Apr 2014 10:48:44 GMT

Replace Previous by Peek

,if([Working Day Flag] =1 ,Date,Peek([Last Working Day])) AS [Last Working Day]

Re: Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:51:52 GMT

Great, thanks Anbu. works perfectly!

Richard

Re: Scripting - Check Last Working Day

lironbaram — Tue, 22 Apr 2014 10:53:40 GMT

yes this is my mistake you need to use peek and not previous

that way you can relate to the field himself in the function

so it should look like this

if([Working Day Flag] =1 ,Date,peek('[Last Working Day]')) AS Last Working Day

Re: Scripting - Check Last Working Day

richard_pearce6 — Tue, 22 Apr 2014 10:56:30 GMT

Thanks Liron, I appreciate your response and time spent on this,

Richard