Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
)
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;
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;
Perfect solution!. Thanks for the tip!
You're welcome
Actually this is the first practical use I have had for the Evaluate() function ...
That is amazing! I spent hours of trying everything and finally got that Evaluate trick! Thank you! Brilliant solution!