Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hykal1993
Contributor II
Contributor II

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

Hi,

I am trying to exclude public holidays and weekends in the bolded calculation below. Can anyone help please?

LOAD

[Holiday Date]

FROM

LOAD

[Issue ID],

[Notification Date],

[Date Issue Identified],

[Notification Date] - [Date Issue Identified] as [Working Days]

FROM

11 Replies
sibusiso90
Creator III
Creator III

HI please find the solution below.

LOAD

[Holiday Date]

FROM

LOAD

[Issue ID],

[Notification Date],

[Date Issue Identified],

if(not match([Notification Date],[Date Issue Identified]),[Notification Date]) as [Working Days]

FROM

hykal1993
Contributor II
Contributor II
Author

Do you mean below? I tried, it says 'Holiday Date not found'

LOAD

[Holiday Date]

FROM

[source file]

LOAD

[Issue ID],

[Notification Date],

[Date Issue Identified],

if(not match([Notification Date],[Date Issue Identified]),[Holiday Date]) as [Working Days]

FROM

[source file]

sibusiso90
Creator III
Creator III

Try this if it complains just use not exist([Holiday Date],[Notification Date]) instead of what is below. This should solve all your problems.

LOAD

[Holiday Date]

FROM

LOAD

[Issue ID],

[Notification Date],

[Date Issue Identified],

if(not match([Notification Date],[Date Issue Identified]),[Notification Date]) as [Working Days]

FROM

[source file]

where not exist([Notification Date],[Holiday Date])

hykal1993
Contributor II
Contributor II
Author

Do you mean like below? Seems odd, I have tried to reload, got error

test 4.PNG

sibusiso90
Creator III
Creator III

Yes that is what I mean what was your error?

hykal1993
Contributor II
Contributor II
Author

test 5.PNG

sibusiso90
Creator III
Creator III

pull the information first then use a resident load when removing holidays

hykal1993
Contributor II
Contributor II
Author

How to do that?

If it's too difficult, I can put it in expression. Thanks.

sibusiso90
Creator III
Creator III

Hi

It is straight forward.

e.g.

T1

Load

     *

from me.qvd(qvd);

noconcatinate

T2

Load

     *

resident T1;

drop table T1;