0 Replies Latest reply: Oct 10, 2017 8:50 AM by Marian Szetyinszki RSS

    Passing holiday list argument to Networkdays from a field

    Marian Szetyinszki

      I have a qvd file as a source of the holidays in the format of a comma separated holiday list per each country.

       

      I add this field (with holiday list) as a new field in my data containing the dates I want to perform calculation on.

       

      I want to calculate a field using Networkdays() function in the script and want to pass the holiday list  as the function argument from the field added (it is already part of the data when calculation is done).

       

      While this works if I read the holiday list field first into a variable and pass this variable using $ sign expansion  in the script, but it does not work  if I pass the list directly from the field .

       

      What is the correct way of passing a string list of holidays?  I want to avoid cycling through the data in my load via defining variable per each country ,  as the  performance gets dramatically down on large data sets this way.

      Example of the holiday list and fields:

      example.JPG

      This formula in script would not consider the holiday list:

      NetWorkDays( Depot In,Depot Out,HL)

       

      But this one would work:

       

      NetWorkDays( Depot In,Depot Out,$(vHolTest))

      where

      vHolTest was previously defined  from the same table using Let /Peek: