Qlik Community

QlikView Documents

Documents for QlikView related information.

Master Calendar with Working days & Holidays Flag

MVP
MVP

Master Calendar with Working days & Holidays Flag

HI All,

The below script helps in creating the Master Calendar with Working & Non working (Holiday) flags.  Holidays are loaded from the Excel file.

LET vWeeklyHolidays = 'Sat;Sun';   // Adjust weekly holidays here, currently considered Saturday & Sunday as holiday, based on your requirement change it accordingly

CalendarMaster_Temp:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter, 

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    WeekDay(InvoiceDate) AS WeekDay, 

    Week(InvoiceDate) As Week;

Load

     Date(MinDate + IterNo() -1 ) AS InvoiceDate  // Replace InvoiceDate with your actual date field name

While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(InvoiceDate) AS MinDate,

    Max(InvoiceDate) AS MaxDate    // Replace InvoiceDate with your actual date field name

RESIDENT Invoice;                 /// Replace Invoice with your actual Transaction Table.

LEFT JOIN(CalendarMaster_Temp)

LOAD Date(Holiday) AS InvoiceDate,

     Description AS HolidayDescription,

     1 AS IsHolidayFlag

FROM

[List of Holidays.xlsx]   // Load holiday details from Excel file attached, if you have this info in database tables change it accordingly

(ooxml, embedded labels, table is Holidays); 

Calendar:

LOAD

  InvoiceDate,

    Year,

    Quarter, 

    Month,

    Day,

    WeekDay, 

    Week,

    HolidayDescription,

  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,

  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType

Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;     

 

Regards,

jagan.


Attachments
Comments
vikasmahajan
Honored Contributor III

Thanks for Sharing

Vikas

imrencimen
New Contributor III

thanks

Not applicable

Superb post!

Version history
Revision #:
1 of 1
Last update:
‎03-13-2016 11:42 PM
Updated by: