Holiday Calendar Flag

[sat_tok52]

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

[YoussefBelloum]

Hi,

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

[sat_tok52]

Hi Youssef,

Thanks for quick reply,

That is sample data, need to create a flag.

Thanks,

Sateesh.T

[sunny_talwar]

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

[sat_tok52]

Hi Sunny,

Thanks for quick response.

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

Thanks,

Sateesh.T

[sunny_talwar]

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?

[sat_tok52]

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

[sat_tok52]

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

[sunny_talwar]

Okay this makes sense....

[sunny_talwar · Accepted solution]

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;