topic Re: Holiday Calendar Flag in QlikView

Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 09:59:37 GMT

Hi All,

I have to create a flag for Holiday days,suppose 1-1-2018 holiday it should show as 1 and next working days also show flag 1 and if 04-01-2018 holiday it should show as 2 next working day should show 2.Below is the example.

Date Flag

1-1-2018 1 Holiday

2-1-2018 1 Working Day

3-1-2018 1 Working Day

4-1-2018 2 Holiday

5-1-2018 2 Working Day

6-1-2018 3 Holiday

7-2-2018 3 Working Day

Any Help?

Thanks,

Sateesh.T

Re: Holiday Calendar Flag

YoussefBelloum — Fri, 16 Feb 2018 10:20:47 GMT

Hi,

is that your data ? you want to clean the Flag column ? or create it ?

Re: Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 10:33:07 GMT

Hi Youssef,

Thanks for quick reply,

That is sample data, need to create a flag.

Thanks,

Sateesh.T

Re: Holiday Calendar Flag

sunny_talwar — Fri, 16 Feb 2018 10:42:12 GMT

So, I am guessing you need to create this flag in a master calendar? What is the source of all the holidays?

Re: Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 10:58:46 GMT

Hi Sunny,

Thanks for quick response.

Storing the list of holidays into excel i am calling that excel in qlikview.

Thanks,

Sateesh.T

Re: Holiday Calendar Flag

sunny_talwar — Fri, 16 Feb 2018 11:03:51 GMT

2 more questions

1) You need the flag to say 1 Holiday... 15 Holiday and 1 Working Day .... 215 Working Day....?

2) Why is 2-1-2018 and 3-1-2018 both 1 Working Day?

Re: Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 11:12:49 GMT

Hi Sunny,

I Just explain the scenario my above script is just for example.

Date Flag

1-12018 1

6-1-2018 2

7-1-2018 3

13-1-2018 4

14-01-2018 5

Thanks,

Sateesh.T

Re: Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 11:40:16 GMT

Hi Sunny,

1)The output of the flag should be like below.

Flag,

1 --------------------1-1-2018 to 5-1-2018

2 --------------------6-1-2018

3--------------------7-1-2018 to 12-1-2018

4-------------------13-1-2018

5-------------------14-1-2018 to 16-1-2018

6-------------------17-1-2018 to 19-1-2018(17 was a holiday)

7-------------------20-1-2018

8-------------------21-1-2018 to 25-1-2018

9------------------ 26-1-2018(holiday)

2)if 1st was holiday (1-1-2018) to next holiday (6-1-2018) in between 1st holiday to next holiday all the working days should be flag as 1

Re: Holiday Calendar Flag

sunny_talwar — Fri, 16 Feb 2018 11:43:29 GMT

Okay this makes sense....

Re: Holiday Calendar Flag

sunny_talwar — Fri, 16 Feb 2018 11:52:46 GMT

Try something like this

Holidays:

LOAD * INLINE [

Holidays

01/01/2018

01/04/2018

01/06/2018

];

Calendar:

LOAD *,

If(Exists(Holidays, Date), 1, 0) as HolidayFlag;

LOAD Date(YearStart(Today()) + IterNo() - 1) as Date

AutoGenerate 1

While IterNo() <= 31;

MasterCalendar:

LOAD *,

If(HolidayFlag = 1, RangeSum(Peek('Flag'), 1), Alt(Peek('Flag'), 1)) as Flag

Resident Calendar

Order By Date;

DROP Table Calendar;

Re: Holiday Calendar Flag

sat_tok52 — Fri, 16 Feb 2018 12:57:36 GMT

Hi Sunny,

Thanks.