topic NetWorkDays() Query in QlikView

NetWorkDays() Query

Thu, 19 Nov 2009 15:40:34 GMT

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.

NetWorkDays() Query

Anonymous — Thu, 19 Nov 2009 15:46:56 GMT

Hi Arun,

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

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

NetWorkDays() Query

rsark — Wed, 02 Dec 2009 01:48:23 GMT

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

NetWorkDays() Query

Wed, 02 Dec 2009 12:20:37 GMT

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.

NetWorkDays() Query

rsark — Fri, 04 Dec 2009 04:55:22 GMT

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)
;

NetWorkDays() Query

Tue, 13 Apr 2010 16:06:23 GMT

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