Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Re: To exclude the public holidays based on a holiday table loaded to create variable calculation

Check if that helps How to load all files excluding *Public Holiday* and *Week End* script in QlikSense

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
hykal1993
New Contributor

Re: To exclude the public holidays based on a holiday table loaded to create variable calculation

My script is actually a bit more complicated. Now I'm a bit lost in the order of the script. Does below correct?

Can you help check and fix it? Thank you sooo much

'Holiday':

LOAD [Holidays Date],

     Day,

     Holiday,

     [Applicable to]

FROM

(ooxml, embedded labels, table is Holidays);

'Validated Closed':

LOAD

     [CIM ID],

     [A&A Team],

     [Month-Year],

FROM

(ooxml, embedded labels, table is Sheet1);

'CIM':

LOAD

         [Open Date],

         [Sample Month],

         [Notification Date],

         [Date Issue Identified],

         if(isNull(Lookup('A&A Team', 'CIM ID', [Issue ID], 'Validated Closed')), 'X', '/') as [Is this Validated Closed CIM]

FROM

(ooxml, embedded labels, table is Report);

LOAD

*,

     If(year([Open Date])=year([Sample Month]) and month([Open Date])=month([Sample Month]), '/','X') as [V1-5]

LOAD

*,

IF([V1-5]='X' or [Is this Validated Closed CIM]='/','NA',

IF([Date Issue Identified]>[Notification Date],NETWORKDAYS([Date Issue Identified],[Notification Date],[Holidays Date])+1,

IF([Date Issue Identified]<[Notification Date],NETWORKDAYS([Date Issue Identified],[Notification Date],[Holidays Date])-1,

IF([Date Issue Identified]=[Notification Date],0,'')))) as [Number of days]

Resident Holiday;

Drop Holiday;