Discussion board where members can learn more about Qlik Sense App Development and Usage.
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:
Networkdays([Depot In],[Depot Out], HL) as NetDays
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 :
Go to 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:
LOAD * INLINE [
Depot In;Depot Out;HL
] (delimiter is ';', no quotes);
Evaluate('Networkdays('&Chr(39)&[Depot In]&Chr(39)&','&Chr(39)&[Depot Out]&Chr(39)&','&HL&')') AS NWD
DROP TABLE holidayTable;
Perfect solution!. Thanks for the tip!
Actually this is the first practical use I have had for the Evaluate() function ...