Quick and easy calendar with Python and QlikView

Let’s create a calendar using Python and QlikView

QlikView document

It’s often needed to create calendar based reports so it would be nice to have a calendar already in place and ready to copy/paste into your QlikView reports script

The idea is to use Python to generate a CSV file containing the holidays in the required timeframe and then I will use QlikView to ETL the data and create the report

Since I am currently based in Italy I will consider the working days/non working days based on Italian legislation, for example for year 2020:

• New Year’s Day (Capodanno) — 01/01/2020
• Epiphany (Epifania) — 06/01/2020
• Easter Monday (Lunedì di Pasqua) — 13/04/2020
• Liberation Day (Liberazione dal nazifascismo) — 25/04/2020
• Labour Day (Festa del lavoro) — 01/05/2020
• Republic Day (Festa della Repubblica) — 02/06/2020
• Assumption of Mary (Assunzione di Maria) — 15/08/2020
• All Saint’s Day (Ognissanti) — 01/11/2020
• Saint Ambrose Patron of Milano (Sant’Ambrogio Patrono di Milano) — 07/12/2020 (only valid for Milano)
• Immaculate Conception (Immacolata Concezione) — 08/12/2020
• Christmas Day (Natale di Gesù) — 25/12/2020
• St. Stephen’s Day (Santo Stefano) — 26/12/2020

Let’s have a look at the Python code:

Python script

The library holidays comes very handy for this project

Basically the steps are:
1 take as input start_year and end_year if present
2 loop through the years and add to a list the dictionary given by the holidays module
3 convert the list of dictionaries into a dictionary
4 write the records of the dictionary into a CSV file in the format Year, Month, Day, Name

Now let’s have a look at the QlikView script:

Basically the steps are:
1 load the data from the holiday CSV file computing the date_id
2 create a map for the holidays
3 get the starting and ending years
4 auto generate the date_id
5 load the final table with all the required fields and compute the day_type field using the WEEKDAY function to check for weekends and the map holidays to check for the holidays

The QlikView script is based on this awesome post from mov on community.qlik.com

The layout is very basic and could be improved for example adding an actual calendar view coloring the holidays in red and the weekends in grey like a real calendar

Note: Code can be found in this github repository