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

[mszetyinszki]

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

)

[petter · Accepted solution]

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]

Perfect solution!. Thanks for the tip!

[petter]

You're welcome

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

[spetkov009]

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