Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
upendrarajeshir
Contributor II
Contributor II

Exclude holidays using Networkdays() in script

Hi All,

I tried to calculate actual worked days using NetWorkDays(). I have country wise list of start date and end dates and have country wise list of holidays. Each country has different holidays. Now I want to calculate Actual Worked Days country wise .

I have created one chart in the attached QVW file, NetWorkDays() executes perfectly when I create variable but I want to do it in script so that it will calculate Actual Worked Days even if when I do not have selection over the country.

I attached sample Qvw and excel file.

Thanks in advance !!!

Regards,

Upendra

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi Upendra,

This quite hard script, but I'm sure it work

Country:

LOAD Concat(DISTINCT chr(39) & Country &chr(39),',') AS CountryName

FROM

Book1.xlsx

(ooxml, embedded labels, table is CountryWorkDates);

Let vCountryList = Peek('CountryName',0,'Country');

DROP Table Country;

For Each vCountry in $(vCountryList)

  [Holiday]:

  LOAD Concat(chr(39) & Holiday &chr(39),',') AS HD

  FROM Book1.xlsx (ooxml, embedded labels, table is CountryHolidays) Where Country='$(vCountry)';

  Let vHoliday = Peek('HD',0,'Holiday');

  DROP Table Holiday;

  CountryWorkDates:

  LOAD

  Country,

      StartDate,

    EndDate,

      NetWorkDays(StartDate,EndDate,$(vHoliday)) as ActualDays

  FROM

  Book1.xlsx

  (ooxml, embedded labels, table is CountryWorkDates) Where Country='$(vCountry)';

NEXT vCountry

Let vCountry = Null();

Let vCountryList = Null();

let vHoliday = Null();

See attached file

Regards,

Sokkorn

View solution in original post

3 Replies
Sokkorn
Master
Master

Hi Upendra,

This quite hard script, but I'm sure it work

Country:

LOAD Concat(DISTINCT chr(39) & Country &chr(39),',') AS CountryName

FROM

Book1.xlsx

(ooxml, embedded labels, table is CountryWorkDates);

Let vCountryList = Peek('CountryName',0,'Country');

DROP Table Country;

For Each vCountry in $(vCountryList)

  [Holiday]:

  LOAD Concat(chr(39) & Holiday &chr(39),',') AS HD

  FROM Book1.xlsx (ooxml, embedded labels, table is CountryHolidays) Where Country='$(vCountry)';

  Let vHoliday = Peek('HD',0,'Holiday');

  DROP Table Holiday;

  CountryWorkDates:

  LOAD

  Country,

      StartDate,

    EndDate,

      NetWorkDays(StartDate,EndDate,$(vHoliday)) as ActualDays

  FROM

  Book1.xlsx

  (ooxml, embedded labels, table is CountryWorkDates) Where Country='$(vCountry)';

NEXT vCountry

Let vCountry = Null();

Let vCountryList = Null();

let vHoliday = Null();

See attached file

Regards,

Sokkorn

View solution in original post

upendrarajeshir
Contributor II
Contributor II
Author

Hi Sokkom,

Its tricky but works for me. Thanks a lot !!!

Regards,

Upendra

radupanciuc
Contributor II
Contributor II

Thanks for posting this. It seems to be working for me as well but I do have another challenge.

Is it possible to set up a logic that if the End Date is a holiday (from the holiday list) then exclude that day from the holiday list? What I mean is the following:

Holiday: 5 Sep 2017

Using your script, Qlik Sense returns the following:

  

Start DateEnd DateActualDays
2017.09.042017.09.041
2017.09.042017.09.051

Is it possible to return 2 days for the scenario when the End Date is on a Holiday like below:

  

Start DateEnd DateActualDays
2017.09.042017.09.041
2017.09.042017.09.05

2

Any help is appreciated!