topic Re: [Discussion] NetWorkDays function, holidays argument format in QlikView

[Discussion] NetWorkDays function, holidays argument format

Wed, 24 Aug 2016 08:11:43 GMT

Hello community, I have started this discussion in order to get a question solved that me and some other members found on another thread and I think that might be of general interest.

On my way on finding a optimal way of calculating the networkdays between two dates but without including the holidays for differetn countries (see thread: Re: Bank holidays for multiple countries) I have prepared the following script in order to test the function:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';


HOLIDAYS:
LOAD * INLINE [START_DATE, END_DATE, HOLIDAY_DAYS
'1/2/2016', '13/2/2016', '10/02/2016',
'1/2/2016', '13/2/2016', '11/02/2016',
'1/2/2016', '13/2/2016', '9/02/2016'];


LEFT JOIN(HOLIDAYS)
LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS
RESIDENT HOLIDAYS;


LEFT JOIN(HOLIDAYS)
LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL,
     NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL
RESIDENT HOLIDAYS;

The results obtained from running the script:

As you can see, the field WITH_HOL is not properly calculated. So if we look at the formula LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL, the field 'HOLIDAYS' is not beeing understood by the function. So my question is, why? if you put the content of the field into a variable, is properly understood but you loose the 'dynamic' property of the field, I mean, the multiple possible values for each country or customer the field may have. Until now, the only way to make the parameter switch is to perform a loop but for large amounts of data this is not efficient.
If someone can provide me an answer or a solution. Thank you all in advance.

Re: [Discussion] NetWorkDays function, holidays argument format

jpenuliar — Wed, 24 Aug 2016 08:59:20 GMT

Hi Diana,

Have you looked at below logic:

Match countries to get holidays for NetWorkDays formula

Re: [Discussion] NetWorkDays function, holidays argument format

Wed, 24 Aug 2016 09:04:09 GMT

Yeah, I have checked this and this is how I have implemented in my script already but please note that I have a very large amount of data and also I have to perform a double loop as I have not only to iterate through many countries but also trough service centers (and it's holidays) so the time that my script takes to reload is very large and it must be reloaded few times each day. I'm searching for a efficient way to implement the same, maybe a macro? My problem is I know nothing about VB...

Thanks for your response
D.

Re: [Discussion] NetWorkDays function, holidays argument format

Wed, 24 Aug 2016 09:06:21 GMT

Do you wanna try variables?

HOLIDAYS:

LOAD * INLINE [START_DATE, END_DATE, HOLIDAY_DAYS

'1/2/2016', '13/2/2016', '10/02/2016',

'1/2/2016', '13/2/2016', '11/02/2016',

'1/2/2016', '13/2/2016', '9/02/2016'];

Temp:

LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS

RESIDENT HOLIDAYS;

let HOLIDAYSVAR = PEEK('HOLIDAYS');

LEFT JOIN(HOLIDAYS)

LOAD NETWORKDAYS(START_DATE, END_DATE, $(HOLIDAYSVAR)) AS WITH_HOL,

NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL

RESIDENT HOLIDAYS;

Re: [Discussion] NetWorkDays function, holidays argument format

Anonymous — Wed, 24 Aug 2016 09:09:40 GMT

you can use a workaround

store your Holidays into a variable and use this variable

e.g.

LEFT JOIN(HOLIDAYS)
LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS
RESIDENT HOLIDAYS;
//new:
let vholidays=peek('HOLIDAYS',0);

LEFT JOIN(HOLIDAYS)
LOAD NETWORKDAYS(START_DATE, END_DATE, $(vholidays)) AS WITH_HOL,
NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL
RESIDENT HOLIDAYS;

Re: [Discussion] NetWorkDays function, holidays argument format

Wed, 24 Aug 2016 09:14:49 GMT

Thanks for your answer but is not what I'm searching for as I need the Holidays to be different for each country and variables (especially those with LET) don't change once evaluated (they have a fixed value) so the same holidays will be evaluated for all the countries.

Re: [Discussion] NetWorkDays function, holidays argument format

jpenuliar — Wed, 24 Aug 2016 10:30:03 GMT

You mentioned, each Country has holiday dates, in your table HOLIDAYS, I dont see any field showing a country.

As the others have already mentioned, you can come up with variables for each countries, this is practical only when you have a number of countries you can count with your fingers.

Re: [Discussion] NetWorkDays function, holidays argument format

Wed, 24 Aug 2016 10:34:49 GMT

In the main message I've linked a thread that I did asking for a way to perform holidays for multiple countries and there's data sample. I made this thread because I noticed, networkdays() function is not working properly when using a field with various holidays (field created by concat) and how could I make it work. Thanks for all your kind responses, I'll keep thinking on a suitable solution, if you have any other proposition ill be glad to try it!

Re: [Discussion] NetWorkDays function, holidays argument format

jpenuliar — Wed, 24 Aug 2016 10:57:05 GMT

Can you post a sample data/file?

Re: [Discussion] NetWorkDays function, holidays argument format

Thu, 25 Aug 2016 14:35:36 GMT

Let's imagine that my data consists on a table like this:

CUSTOMER_DATA
IDCustomer
CustomerCountry
StartDate
EndDate

and I wish to add a new field, obtained in the following way:

ServiceTime = NetWorkDays(StartDate, EndDate)

In this new field I wish to obtain the bussiness days the service has been active BUT taking into account the holidays for each IDCustomer and each country. I mean, for every IDCustomer there will exist a different number of holidays between their StartDate and their EndDate. If I have and Excel file with all the bank holidays for each country, wich I can load into my script, like:

COUNTRY_ID	BANK_HOLIDAY_DATE
Poland	12/03/2016
Poland	13/03/2016
...	...

In reality is not so simple because StartDate and EndDate are calculated checking some other fields but this is an approximation. Thanks for your responses.

Re: [Discussion] NetWorkDays function, holidays argument format

jpenuliar — Thu, 25 Aug 2016 17:58:14 GMT

DATA:

LOAD IDCustomer,

	CustomerCountry,
	[Start Date],
	[End Date]

FROM

Sample.xlsx

(ooxml, embedded labels, table is DATA);

HOLIDAYLIST:

LOAD COUNTRY,

concat(chr(39) & DATE & chr(39),',') as List

FROM

Sample.xlsx

(ooxml, embedded labels, table is [Bank Holiday])

Group by COUNTRY;

let noRows = NoOfRows('HOLIDAYLIST')-1;

for i=0 to $(noRows)

	let vCountry = peek('COUNTRY',$(i),'HOLIDAYLIST');
	let vBankHolidays = peek('List',$(i),'HOLIDAYLIST');

	TEST:
	LOAD
		IDCustomer,
		CustomerCountry,
		[Start Date],
		[End Date],
		NetWorkDays([Start Date],[End Date]) as ServiceTime ,
	NetWorkDays([Start Date],[End Date],$(vBankHolidays)) as ServiceTime_holiday_variable
	Resident DATA
	Where
	CustomerCountry = '$(vCountry)'
	;

next i;

Drop table HOLIDAYLIST;

Drop table DATA;