NetWorkDays() Query

[#1]

Hi Team,

When I give

NetWorkDays('2009-08-17','2009-08-24', '2009-01-08','2009-01-26','2009-02-23','2009-03-10','2009-08-19') ----its giving 5...

but instead of fixed values, if i use a variable for Holiday list like

NetWorkDays('2009-08-17','2009-08-24', strHolidays)----its giving 6..means its not considering the Holidays list.

Variable contain the same set of values in the same format..;its like first we are creating a variable...

strHolidays = '2009-01-08','2009-01-26','2009-02-23','2009-03-10','2009-08-19'

and then we write

NetWorkDays('2009-08-17','2009-08-24', strHolidays)

Why??

Please suggest!!

Any other alternative please.

[Anonymous · Accepted solution]

Hi Arun,

Try this instead with $() around the variable name:

NetWorkDays('2009-08-17','2009-08-24', $(strHolidays))

[rsark]

Hello,

I am attempting to populate the $(strHolidays) variable within the script. I created a resident calendar table, but the date format is 'Date1' for each date with no commas. I cannot seem to get the necessary formatting set. (ie.. 'date1' , 'date2' , 'date3', etc..) Were you able to come up with a solution or can you provide any suggestions as to how I can accomplish this? The plan is to use the "NETWORKDAYS" function with $(strHoldays) further down in the script.

Thanks,

Ron

[#4]

Hi Ron,

To populate strHoliday variable in the script, u can do like below:

HolidayMst:
LOAD Concat(Date(Date,'MM/DD/YYYY'),';') as AllHolidays
FROM Holidays.xls (biff, embedded labels, table is Sheet1$);

Let strAllHolidays = Chr(39) & Replace(Peek('AllHolidays',0,'BankHolidayMst'),';',Chr(39) & ',' & Chr(39)) & Chr(39);

Chr(39) represents Single Quote character.

Now strAllHolidays will contain the holiday list in the desired format.

Suggest you to take caution while use this variable into NetWorkDays() as the function will take the variable in '$(strHoliday)' format and we have single quote embedded already in the holiday list in start and end.

[rsark]

The tweaked the example you provided and it worked like a charm. Thanks much for you quick reply

HOLIDAYSTR:




















LOAD





Concat(Date("HOLIDAY_DATE",'YYYY-MM-DD'),';') as
ALLHOLIDAYS;






SQL



 SELECT "HOLIDAY_DATE" FROM
DATAMART.HOLIDAYS;






LET



 strALLHOLIDAYS = Chr(39) & Replace(Peek('ALLHOLIDAYS',0,'HOLIDAYSTR'),';',Chr(39) & ',' & Chr(39)) & Chr(39)
;

[#6]

Perfect your answer dear friend:

I was solved so:

FeriadosSTR:
LOAD
Concat(Date("Data",'DD/MM/YYYY'),';') as Feriados
FROM [$(Diretório)\FeriadosNacionais.xls]
(biff, embedded labels, table is [2010$]);
LET strALLFeriados = Chr(39) & Replace(Peek('Feriados',0,'FeriadosSTR'),';',Chr(39) & ',' & Chr(39)) & Chr(39);

Then simply use the following expression:

networkdays ([Dt1], [Dt2], $(strALLFeriados))

Grateful