Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Match countries to get holidays for NetWorkDays formula

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 codeCountry CodeStart dateEnd date
UK111111

UK

31-12-2014 12:002-1-2015 11:00
UK222222UK2-1-2015 10:006-1-2015 14:00
FR111111FR30-12-2014 9:306-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 CodeHoliday dates
UK1-1-2015
UK3-1-2015
UK5-1-2015
FR1-1-2015
FR4-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!

2 Replies
MVP
MVP

Re: Match countries to get holidays for NetWorkDays formula

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!

mov
Esteemed Contributor III

Re: Match countries to get holidays for NetWorkDays formula

Congrats with the book Oleg!

Community Browser