topic Re: Match countries to get holidays for NetWorkDays formula in QlikView

Match countries to get holidays for NetWorkDays formula

Thu, 05 Feb 2015 19:38:40 GMT

Hello all,

I'm currently working on a Qlikview script, but I'm struggling to figure out a stable NetWorkDays formula in the script.

Let me explain what I'm trying to get.

I have 2 Excel files. File A has the following columns:

- Identical code (first 2 characters of this code are identical to the country code).

- Country code (i.e. UK, US, CH, CZ, NL, FR)

- Start date and time

- End date and time

(example file A)

Identical code	Country Code	Start date	End date
UK111111	UK	31-12-2014 12:00	2-1-2015 11:00
UK222222	UK	2-1-2015 10:00	6-1-2015 14:00
FR111111	FR	30-12-2014 9:30	6-1-2015 3:15

File B has the following columns:

- Country code (identical to the country code column in file A)

- Holiday dates

(example file B)

Country Code	Holiday dates
UK	1-1-2015
UK	3-1-2015
UK	5-1-2015
FR	1-1-2015
FR	4-1-2015

For the networkdays I obviously use start and end date of file A.

For the holidays part, I can't get it to match the country code of file A with file B, in order to get all the holiday dates for the specific country.

Since there are a lot of countries and holiday dates in file B, I don't want to use INLINE.

Can anybody help me out?

Thanks!

Re: Match countries to get holidays for NetWorkDays formula

Oleg_Troyansky — Thu, 05 Feb 2015 20:29:37 GMT

The syntax of NetWorkDays allows the list of Holidays to be added at the end. In order to make it work, the list of holidays needs to be saved in a variable (not in a field). Here is what I would do in this case:

1. Load the Holidays per Country with a GROUP BY clause, using the Concat() function to generate the list of all Holidays for each Country. You will need to play with the exact formatting to get it right for the NetWorkDays()

2. Read the resulting summary table line by line, fetching the list of Holidays and the Country into two variables, using Peek().

3. For each Country, read the first table with the WHERE clause (filtering on the Country field). Use the variable with Holidays in the NetWorkDays function. Something along these lines:

LOAD

Country,

NetWorkDays(StartDate, EndDate, $(vHolidays) as WorkDays

reisdent

Tab1

where

Country = '$(vCountry)'

Repeat this logic in the loop for all countries that exist in the summarized second table.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - available to pre-order on Amazon!

Re: Match countries to get holidays for NetWorkDays formula

Anonymous — Thu, 05 Feb 2015 21:16:53 GMT

Congrats with the book Oleg!