Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
mszetyinszki
Contributor II
Contributor II

Question: Is it possible to use a field as a parameter for Networkdays() function?

I load a simple table with 3 fields: StartDate (Depot In) , EndDate (Depot Out)  , and coma separated list of Holiday dates (HL) (example line):

netwokdays.JPG.

I want to calculate Networkdays during load script from this table:

LOAD

CUSTOMER_COUNTRY,

Networkdays([Depot In],[Depot Out], HL) as NetDays

Resident holidayTable;

Help for the function says:

networkdays (start_date, end_date [, holiday])

BUT: The script result does not exclude holidays, despite the field HL is a list of coma separated dates. I get the same result, as if i did not provide the list of holiday dates.

What is the correct way of passing the function argument from an existing field?

Qlik help just states: holiday list is " Holiday periods to exclude from working days...

separated by commas.

Example: '25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'


My field HL is exactly the coma separated list as in the example.


Is it a bug in the function, or the HL field must be passed in a different way?


I want to avoid using variable/cycle through the data, because this is very unproductive script when one works with millions of rows of data. (I do not want the script as in the example :

https://community.qlik.com/thread/106943

)

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

You are trying to pass the list of holidays as a single parameter. The Networkdays() function will only allow multiple holidays to be passed as multiple parameters.

There is a way around this by using the Evaluate() function however. Like this:

holidayTable:

LOAD * INLINE [

Depot In;Depot Out;HL

1.5.2018; 31.5.2018;'1.5.2018','17.5.2018'

1.12.2017; 31.12.2017;'24.12.2018','25.12.2017','26.12.2017','31.12.2017'

] (delimiter is ';', no quotes);


D2:

NOCONCATENATE LOAD

  [Depot In],

  [Depot Out],

  Evaluate('Networkdays('&Chr(39)&[Depot In]&Chr(39)&','&Chr(39)&[Depot Out]&Chr(39)&','&HL&')') AS NWD

RESIDENT

  holidayTable;


DROP TABLE holidayTable;

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

You are trying to pass the list of holidays as a single parameter. The Networkdays() function will only allow multiple holidays to be passed as multiple parameters.

There is a way around this by using the Evaluate() function however. Like this:

holidayTable:

LOAD * INLINE [

Depot In;Depot Out;HL

1.5.2018; 31.5.2018;'1.5.2018','17.5.2018'

1.12.2017; 31.12.2017;'24.12.2018','25.12.2017','26.12.2017','31.12.2017'

] (delimiter is ';', no quotes);


D2:

NOCONCATENATE LOAD

  [Depot In],

  [Depot Out],

  Evaluate('Networkdays('&Chr(39)&[Depot In]&Chr(39)&','&Chr(39)&[Depot Out]&Chr(39)&','&HL&')') AS NWD

RESIDENT

  holidayTable;


DROP TABLE holidayTable;

mszetyinszki
Contributor II
Contributor II
Author

Perfect solution!. Thanks for the tip!

petter
Partner - Champion III
Partner - Champion III

You're welcome

Actually this is the first practical use I have had for the Evaluate() function ...

spetkov009
Contributor II
Contributor II

That is amazing! I spent hours of trying everything and finally got that Evaluate trick!  Thank you! Brilliant solution!