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.