2 Replies Latest reply: Feb 5, 2015 4:16 PM by Michael Solomovich RSS

    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!

        • Re: Match countries to get holidays for NetWorkDays formula
          Oleg Troyansky

          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!