topic Re: Question: Is it possible to use a field as a parameter for Networkdays() function? in App Development

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

mszetyinszki — Tue, 19 Jun 2018 11:17:12 GMT

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

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

)

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

petter — Tue, 19 Jun 2018 19:42:17 GMT

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;

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

mszetyinszki — Wed, 20 Jun 2018 06:54:11 GMT

Perfect solution!. Thanks for the tip!

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

petter — Wed, 20 Jun 2018 08:42:05 GMT

You're welcome

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

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

spetkov009 — Fri, 05 Feb 2021 10:10:02 GMT

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