Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hykal1993
New Contributor

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
Contributor II

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

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

Highlighted
hykal1993
New Contributor

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

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
Contributor II

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

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
New Contributor

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

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

test 4.PNG

sibusiso90
Contributor II

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

Yes that is what I mean what was your error?

hykal1993
New Contributor

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

test 5.PNG

sibusiso90
Contributor II

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

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

hykal1993
New Contributor

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

How to do that?

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

sibusiso90
Contributor II

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

Hi

It is straight forward.

e.g.

T1

Load

     *

from me.qvd(qvd);

noconcatinate

T2

Load

     *

resident T1;

drop table T1;